﻿// Admin.NET 项目的版权、商标、专利和其他相关权利均受相应法律法规的保护。使用本项目应遵守相关法律法规和许可证的要求。
//
// 本项目主要遵循 MIT 许可证和 Apache 许可证（版本 2.0）进行分发和使用。许可证位于源代码树根目录中的 LICENSE-MIT 和 LICENSE-APACHE 文件。
//
// 不得利用本项目从事危害国家安全、扰乱社会秩序、侵犯他人合法权益等法律法规禁止的活动！任何基于本项目二次开发而产生的一切法律纠纷和责任，我们不承担任何责任！

using Admin.NET.Application.Entity;
using Admin.NET.Core.Service;
using Admin.NET.Core;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Math;
using SqlSugar;
using System.ComponentModel;
using Microsoft.AspNetCore.Mvc;
using Admin.NET.Application.Service.PlanViews.Dto;
using System.Linq;

namespace Admin.NET.Application.Service.PlanViews;

[ApiDescriptionSettings(ApplicationConst.GroupName, Order = 100)]
    public class PlanViewService : IDynamicApiController, ITransient
    {
    private readonly SysCacheService _sysCacheService;
    private readonly ISqlSugarClient _sqlSugarClient;
    private readonly PlanListService _planListService;
    private readonly PlanTypeService _planTypeService;
    
    // 新增的模块化服务依赖
    private readonly IDataFilterService _dataFilterService;
    private readonly IStatisticsCardService _statisticsCardService;
    private readonly IMonthlyChartService _monthlyChartService;
    private readonly IAreaAnalysisService _areaAnalysisService;
    private readonly ICategoryAnalysisService _categoryAnalysisService;
    
    /// <summary>
    /// 构造函数 - 重构版本
    /// </summary>
    /// <param name="sqlSugarClient">数据库客户端</param>
    /// <param name="planListService">计划清单服务</param>
    /// <param name="planTypeService">计划类型服务</param>
    /// <param name="sysCacheService">缓存服务</param>
    /// <param name="dataFilterService">数据筛选服务</param>
    /// <param name="statisticsCardService">统计卡片服务</param>
    /// <param name="monthlyChartService">月度图表服务</param>
    /// <param name="areaAnalysisService">区域分析服务</param>
    /// <param name="categoryAnalysisService">分类分析服务</param>
    public PlanViewService(
        ISqlSugarClient sqlSugarClient, 
        PlanListService planListService, 
        PlanTypeService planTypeService, 
        SysCacheService sysCacheService,
        IDataFilterService dataFilterService,
        IStatisticsCardService statisticsCardService,
        IMonthlyChartService monthlyChartService,
        IAreaAnalysisService areaAnalysisService,
        ICategoryAnalysisService categoryAnalysisService)
    {
        _sqlSugarClient = sqlSugarClient;
        _planListService = planListService;
        _planTypeService = planTypeService;
        _sysCacheService = sysCacheService;
        _dataFilterService = dataFilterService;
        _statisticsCardService = statisticsCardService;
        _monthlyChartService = monthlyChartService;
        _areaAnalysisService = areaAnalysisService;
        _categoryAnalysisService = categoryAnalysisService;
    }

    /// <summary>
    /// 周期分析-甘特
    /// </summary>
    [DisplayName("周期分析-甘特")]
    [ApiDescriptionSettings(Name = "gantt")]
    [HttpPost("gantt")]
    public async Task<GanttResponse> GetGantt(GanttRequest req)
    {
        var dims = (req.Dims != null && req.Dims.Count > 0) ? new List<string>(req.Dims) : new List<string> { "ship" };
        var (startDate, endDate) = GetRangeOrWorkTime(req.Filters.RangeStart, req.Filters.RangeEnd, req.Filters.WorkTime);

        // 基础查询与时间过滤，与树形/透视口径一致
        var q = _sqlSugarClient.Queryable<PlanList>()
            .WhereIF(req.Filters.ClassName.HasValue, p => p.ClassName == req.Filters.ClassName)
            .WhereIF(req.Filters.WorkAreaId.HasValue, p => p.WorkAreaId == req.Filters.WorkAreaId)
            .WhereIF(!string.IsNullOrWhiteSpace(req.Filters.Keyword), p =>
                (req.Filters.ShipNos == null || req.Filters.ShipNos.Count == 0) &&
                (req.Filters.Sections == null || req.Filters.Sections.Count == 0) &&
                (
                    SqlFunc.Contains(SqlFunc.IsNull(p.Team, ""), req.Filters.Keyword!.Trim()) ||
                    SqlFunc.Contains(SqlFunc.IsNull(p.Remark, ""), req.Filters.Keyword!.Trim())
                ))
            .WhereIF(req.Filters.OnlyCompleted == true, p => p.ActualEnd != null)
            .ClearFilter<IOrgIdFilter>();

        if (req.Filters.ShipNos != null && req.Filters.ShipNos.Count > 0)
        {
            var keys = req.Filters.ShipNos.Where(s => !string.IsNullOrWhiteSpace(s)).Select(s => s.Trim()).ToList();
            if (keys.Count > 0)
            {
                var exp = Expressionable.Create<PlanList>();
                foreach (var k in keys) exp = exp.Or(p => SqlFunc.Contains(p.Ch, k));
                q = q.Where(exp.ToExpression());
            }
        }
        if (req.Filters.Sections != null && req.Filters.Sections.Count > 0)
        {
            var keys = req.Filters.Sections.Where(s => !string.IsNullOrWhiteSpace(s)).Select(s => s.Trim()).ToList();
            if (keys.Count > 0)
            {
                var exp = Expressionable.Create<PlanList>();
                foreach (var k in keys) exp = exp.Or(p => SqlFunc.Contains(p.Fd, k));
                q = q.Where(exp.ToExpression());
            }
        }

        q = ApplyTimeFilter(q, req.TimeField, req.TimeMatch, startDate, endDate);
        if (req.Filters.IgnoreNegative != false)
        {
            q = q.Where(p =>
                (p.PlanStart == null || p.PlanEnd == null || p.PlanStart <= p.PlanEnd) &&
                (p.ActualStart == null || p.ActualEnd == null || p.ActualStart <= p.ActualEnd));
        }

        // 附加甘特专用时间过滤（独立于 timeField）
        q = q
            .WhereIF(req.PlanStartFrom.HasValue, p => p.PlanStart != null && p.PlanStart >= req.PlanStartFrom)
            .WhereIF(req.PlanStartTo.HasValue, p => p.PlanStart != null && p.PlanStart <= req.PlanStartTo)
            .WhereIF(req.PlanEndFrom.HasValue, p => p.PlanEnd != null && p.PlanEnd >= req.PlanEndFrom)
            .WhereIF(req.PlanEndTo.HasValue, p => p.PlanEnd != null && p.PlanEnd <= req.PlanEndTo)
            .WhereIF(req.ActualStartFrom.HasValue, p => p.ActualStart != null && p.ActualStart >= req.ActualStartFrom)
            .WhereIF(req.ActualStartTo.HasValue, p => p.ActualStart != null && p.ActualStart <= req.ActualStartTo)
            .WhereIF(req.ActualEndFrom.HasValue, p => p.ActualEnd != null && p.ActualEnd >= req.ActualEndFrom)
            .WhereIF(req.ActualEndTo.HasValue, p => p.ActualEnd != null && p.ActualEnd <= req.ActualEndTo);

        var categoryList = await _sqlSugarClient.Queryable<PlanType>().OrderBy(x => x.OrderNo).Select(x => new { x.Id, x.Name }).ToListAsync();
        var categoryDict = categoryList.ToDictionary(x => x.Id, x => x.Name);
        var areaList = await _sqlSugarClient.Queryable<SysOrg>().OrderBy(x => x.OrderNo).Select(x => new { x.Id, x.Name }).ToListAsync();
        var areaDict = areaList.ToDictionary(x => x.Id, x => x.Name);

        // 后端批量展开：一次性构建到目标层级（或全部），避免前端多次请求
        if (req.ExpandAll || (req.ExpandDepth.HasValue && req.ExpandDepth.Value > 0))
        {
            var rowsAll = await q.Select(p => new
            {
                p.Id,
                p.Ch,
                p.Fd,
                p.Team,
                p.ClassName,
                p.WorkAreaId,
                p.PlanStart,
                p.PlanEnd,
                p.ActualStart,
                p.ActualEnd
            }).ToListAsync();

            var full = BuildGanttHierarchy(rowsAll.Cast<dynamic>().ToList(), dims, categoryDict, areaDict, req.OnlyDelayed, out DateTime? minTimeAll, out DateTime? maxTimeAll, out int totalItemsAll);
            int totalRootCount = full.Count;
            int targetDepth = req.ExpandAll ? dims.Count : Math.Max(1, Math.Min(req.ExpandDepth!.Value, dims.Count));
            var pageIdx = Math.Max(1, req.PageIndex);
            var pageSize = Math.Max(1, req.PageSize);
            var pagedRoots = full.Skip((pageIdx - 1) * pageSize).Take(pageSize).ToList();
            TrimTreeToDepth(pagedRoots, targetDepth, dims.Count);

            return new GanttResponse
            {
                Nodes = pagedRoots,
                TimeStart = (minTimeAll ?? startDate),
                TimeEnd = (maxTimeAll ?? endDate),
                TotalItems = totalItemsAll,
                TotalNodes = totalRootCount
            };
        }

        if (req.Lazy)
        {
            var filteredQuery = ApplyPrefixFilters(q, dims, req.ParentKeyPath);
            var lazyRows = await filteredQuery.Select(p => new
            {
                p.Id,
                p.Ch,
                p.Fd,
                p.Team,
                p.ClassName,
                p.WorkAreaId,
                p.PlanStart,
                p.PlanEnd,
                p.ActualStart,
                p.ActualEnd
            }).ToListAsync();

            var (lazyNodes, totalNodes, totalItemsLazy, minLazyTime, maxLazyTime) = BuildLazyGanttNodes(
                lazyRows.Cast<dynamic>().ToList(),
                dims,
                req.ParentKeyPath,
                req.PageIndex,
                req.PageSize,
                categoryDict,
                areaDict);

            var lazyStart = minLazyTime ?? startDate;
            var lazyEnd = maxLazyTime ?? endDate;
            return new GanttResponse
            {
                Nodes = lazyNodes,
                TimeStart = lazyStart,
                TimeEnd = lazyEnd,
                TotalItems = totalItemsLazy,
                TotalNodes = totalNodes
            };
        }

        var rows = await q.Select(p => new
        {
            p.Id,
            p.Ch,
            p.Fd,
            p.Team,
            p.ClassName,
            p.WorkAreaId,
            p.PlanStart,
            p.PlanEnd,
            p.ActualStart,
            p.ActualEnd
        }).ToListAsync();

        // 如果传入了父路径，则仅保留该路径下的记录（用于层级下钻）
        if (req.ParentKeyPath != null && req.ParentKeyPath.Count > 0)
        {
            rows = rows.Where(r =>
            {
                var kp = BuildKeyPath(dims, (object)r);
                if (kp.Count < req.ParentKeyPath!.Count) return false;
                for (int i = 0; i < req.ParentKeyPath!.Count; i++) if (kp[i] != req.ParentKeyPath![i]) return false;
                return true;
            }).ToList();
        }

        List<GanttHierarchyNode> hierarchy;
        DateTime? minTime;
        DateTime? maxTime;
        int totalItems;
        // 合并维度（split）
        if (!string.IsNullOrWhiteSpace(req.CombineDim))
        {
            var idx = dims.FindIndex(d => string.Equals(d, req.CombineDim, StringComparison.OrdinalIgnoreCase));
            if (idx > 0)
            {
                hierarchy = BuildSplitHierarchy(rows.Cast<dynamic>().ToList(), dims, idx, categoryDict, areaDict, out minTime, out maxTime, out totalItems);
            }
            else
            {
                hierarchy = BuildGanttHierarchy(rows.Cast<dynamic>().ToList(), dims, categoryDict, areaDict, req.OnlyDelayed, out minTime, out maxTime, out totalItems);
            }
        }
        else
        {
            hierarchy = BuildGanttHierarchy(rows.Cast<dynamic>().ToList(), dims, categoryDict, areaDict, req.OnlyDelayed, out minTime, out maxTime, out totalItems);
        }

        // 回退到业务范围
        minTime ??= startDate;
        maxTime ??= endDate;

        return new GanttResponse
        {
            Nodes = hierarchy,
            TimeStart = minTime.Value,
            TimeEnd = maxTime.Value,
            TotalItems = totalItems,
            TotalNodes = hierarchy.Count
        };
    }

    // 根据目标维度构建 split 层级：在目标层的父节点下生成一个 Render='split' 的子节点，SplitChildren 为该层各实体的区间
    private static List<GanttHierarchyNode> BuildSplitHierarchy(List<dynamic> rows, List<string> dims, int targetIdx, Dictionary<long, string> categoryDict, Dictionary<long, string> areaDict, out DateTime? minTime, out DateTime? maxTime, out int totalItems)
    {
        minTime = null; maxTime = null; totalItems = 0;
        // 父层前缀长度 = targetIdx（0-based），例如 target=2(section)，父层 keyPath 长度=2（category,ship）
        int parentLen = Math.Max(0, targetIdx);
        // 按父层分组
        var groups = rows
            .GroupBy(r => BuildKeyPath(dims.Take(parentLen).ToList(), (object)r), new KeyPathComparer())
            .ToList();

        var result = new List<GanttHierarchyNode>();
        foreach (var g in groups)
        {
            var parentKey = g.Key;
            // 构建父节点（若 parentLen==0，则父节点即为 split 节点的父级为 root）
            var parentNode = new GanttHierarchyNode
            {
                Id = parentKey.Count > 0 ? string.Join("/", parentKey) : Guid.NewGuid().ToString(),
                Label = (parentKey.Count > 0 ? parentKey.Last() : "root"),
                Level = Math.Max(parentLen - 1, 0),
                KeyPath = parentKey.ToList(),
                DisplayPath = parentKey.ToList(),
                Children = new List<GanttHierarchyNode>(),
            };

            // 目标层的 keyPath 长度 = targetIdx+1
            var childLen = targetIdx + 1;
            var childGroups = g
                .GroupBy(r => BuildKeyPath(dims.Take(childLen).ToList(), (object)r), new KeyPathComparer())
                .ToList();
            var segItems = new List<GanttItem>();
            DateTime? segMin = null, segMax = null;
            foreach (var cg in childGroups)
            {
                // 该子实体的 min/max（实际优先，其次计划）
                DateTime? aStart = null, aEnd = null, pStart = null, pEnd = null;
                foreach (dynamic row in cg)
                {
                    aStart = MinDate(aStart, row.ActualStart);
                    aEnd = MaxDate(aEnd, row.ActualEnd);
                    pStart = MinDate(pStart, row.PlanStart);
                    pEnd = MaxDate(pEnd, row.PlanEnd);
                }
                var start = aStart ?? pStart;
                var end = aEnd ?? pEnd ?? start;
                if (start == null || end == null) continue;
                segMin = MinDate(segMin, start);
                segMax = MaxDate(segMax, end);
                totalItems++;
                segItems.Add(new GanttItem
                {
                    Id = string.Join("/", cg.Key),
                    Label = cg.Key.LastOrDefault() ?? string.Empty,
                    ActualStart = aStart,
                    ActualEnd = aEnd,
                    PlanStart = pStart,
                    PlanEnd = pEnd,
                    KeyPath = cg.Key
                });
            }
            if (segItems.Count == 0) { continue; }
            // split 子节点
            var splitNode = new GanttHierarchyNode
            {
                Id = parentNode.Id + "/__split_" + targetIdx,
                Label = (targetIdx < dims.Count ? dims[targetIdx] : "合并"),
                Level = parentLen,
                KeyPath = parentNode.KeyPath.Concat(new[] { "__split" }).ToList(),
                DisplayPath = parentNode.DisplayPath.Concat(new[] { (targetIdx < dims.Count ? dims[targetIdx] : "合并") }).ToList(),
                Render = "split",
                SplitChildren = segItems,
                ActualStart = segItems.Where(s => s.ActualStart != null).Select(s => s.ActualStart!.Value).DefaultIfEmpty().Min(),
                ActualEnd = segItems.Where(s => s.ActualEnd != null).Select(s => s.ActualEnd!.Value).DefaultIfEmpty().Max(),
                PlanStart = segItems.Where(s => s.PlanStart != null).Select(s => s.PlanStart!.Value).DefaultIfEmpty().Min(),
                PlanEnd = segItems.Where(s => s.PlanEnd != null).Select(s => s.PlanEnd!.Value).DefaultIfEmpty().Max(),
            };
            UpdateRangeFromNode(splitNode, ref minTime, ref maxTime);
            parentNode.Children.Add(splitNode);
            parentNode.HasChildren = true;
            result.Add(parentNode);
        }

        return result;
    }

    private static void TrimTreeToDepth(List<GanttHierarchyNode> nodes, int targetDepth, int dimsCount)
    {
        foreach (var n in nodes)
        {
            // Level 从 0 开始；当 Level+1 达到目标层，清空其 children，但保留 HasChildren 标记（若仍有更深层）
            if (n.Level + 1 >= targetDepth)
            {
                bool hasDeeper = (n.Level + 1) < dimsCount;
                n.Children = new List<GanttHierarchyNode>();
                n.HasChildren = hasDeeper;
            }
            else
            {
                if (n.Children != null && n.Children.Count > 0)
                    TrimTreeToDepth(n.Children, targetDepth, dimsCount);
                n.HasChildren = (n.Level + 1) < dimsCount && (n.Children?.Count >= 0);
            }
        }
    }

    // ========== 周期分析（树形/透视）实现 ==========

    /// <summary>
    /// 周期分析（树形）- 基于 PlanList 聚合，支持任意维度顺序
    /// </summary>
    [DisplayName("周期分析-树形")]
    [ApiDescriptionSettings(Name = "cycleTree")]
    [HttpPost]
    public async Task<CycleTreeResponse> GetCycleTree(CycleTreeRequest req)
    {
        // 1) 解析时间范围：优先自定义，其次业务月（非自然月）
        var (startDate, endDate) = GetRangeOrWorkTime(req.Filters.RangeStart, req.Filters.RangeEnd, req.Filters.WorkTime);

        // 2) 获取基础数据（仅所需字段）
        var baseQuery = _sqlSugarClient.Queryable<PlanList>()
            .WhereIF(req.Filters.ClassName.HasValue, p => p.ClassName == req.Filters.ClassName)
            .WhereIF(req.Filters.WorkAreaId.HasValue, p => p.WorkAreaId == req.Filters.WorkAreaId)
            // 关键词仅在未显式选中船号/分段时，对 Team/Remark 做模糊
            .WhereIF(!string.IsNullOrWhiteSpace(req.Filters.Keyword), p =>
                (req.Filters.ShipNos == null || req.Filters.ShipNos.Count == 0) &&
                (req.Filters.Sections == null || req.Filters.Sections.Count == 0) &&
                (
                    SqlFunc.Contains(SqlFunc.IsNull(p.Team, ""), req.Filters.Keyword!.Trim()) ||
                    SqlFunc.Contains(SqlFunc.IsNull(p.Remark, ""), req.Filters.Keyword!.Trim())
                ))
            .WhereIF(req.Filters.OnlyCompleted == true, p => p.ActualEnd != null)
            .ClearFilter<IOrgIdFilter>();

        if (req.Filters.ShipNos != null && req.Filters.ShipNos.Count > 0)
        {
            var keys = req.Filters.ShipNos.Where(s => !string.IsNullOrWhiteSpace(s)).Select(s => s.Trim()).ToList();
            if (keys.Count > 0)
            {
                var exp = Expressionable.Create<PlanList>();
                foreach (var k in keys) exp = exp.Or(p => SqlFunc.Contains(p.Ch, k));
                baseQuery = baseQuery.Where(exp.ToExpression());
            }
        }
        if (req.Filters.Sections != null && req.Filters.Sections.Count > 0)
        {
            var keys = req.Filters.Sections.Where(s => !string.IsNullOrWhiteSpace(s)).Select(s => s.Trim()).ToList();
            if (keys.Count > 0)
            {
                var exp = Expressionable.Create<PlanList>();
                foreach (var k in keys) exp = exp.Or(p => SqlFunc.Contains(p.Fd, k));
                baseQuery = baseQuery.Where(exp.ToExpression());
            }
        }

        // 船号/分段模糊匹配（支持多关键词 OR）
        if (req.Filters.ShipNos != null && req.Filters.ShipNos.Count > 0)
        {
            var keys = req.Filters.ShipNos.Where(s => !string.IsNullOrWhiteSpace(s)).Select(s => s.Trim()).ToList();
            if (keys.Count > 0)
            {
                var exp = Expressionable.Create<PlanList>();
                foreach (var k in keys) exp = exp.Or(p => SqlFunc.Contains(p.Ch, k));
                baseQuery = baseQuery.Where(exp.ToExpression());
            }
        }
        if (req.Filters.Sections != null && req.Filters.Sections.Count > 0)
        {
            var keys = req.Filters.Sections.Where(s => !string.IsNullOrWhiteSpace(s)).Select(s => s.Trim()).ToList();
            if (keys.Count > 0)
            {
                var exp = Expressionable.Create<PlanList>();
                foreach (var k in keys) exp = exp.Or(p => SqlFunc.Contains(p.Fd, k));
                baseQuery = baseQuery.Where(exp.ToExpression());
            }
        }

        // 时间匹配（若需要全量，可在前端传一个极大范围；此处统一应用）
        baseQuery = ApplyTimeFilter(baseQuery, req.TimeField, req.TimeMatch, startDate, endDate);

        // 负周期过滤
        if (req.Filters.IgnoreNegative != false)
        {
            baseQuery = baseQuery.Where(p =>
                (p.PlanStart == null || p.PlanEnd == null || p.PlanStart <= p.PlanEnd) &&
                (p.ActualStart == null || p.ActualEnd == null || p.ActualStart <= p.ActualEnd));
        }

        // 懒加载：如果开启，根据父路径仅取下一层
        var dims = (req.Dims != null && req.Dims.Count > 0) ? req.Dims : new List<string> { "ship" };
        if (req.Lazy)
        {
            int depth = req.ParentKeyPath?.Count ?? 0; // 将要加载的层级（父之后的下一层）

            // 仅加载根层或某一父节点的直接子节点
            var filtered = ApplyPrefixFilters(baseQuery, dims, req.ParentKeyPath);
            var partRows = await filtered.Select(p => new
            {
                p.Ch,
                p.Fd,
                p.ClassName,
                p.WorkAreaId,
                p.PlanStart,
                p.PlanEnd,
                p.ActualStart,
                p.ActualEnd
            }).ToListAsync();

            var categoryListL = await _sqlSugarClient.Queryable<PlanType>().Select(x => new { x.Id, x.Name }).ToListAsync();
            var categoryDictL = categoryListL.ToDictionary(x => x.Id, x => x.Name);
            var areaListL = await _sqlSugarClient.Queryable<SysOrg>().Select(x => new { x.Id, x.Name }).ToListAsync();
            var areaDictL = areaListL.ToDictionary(x => x.Id, x => x.Name);

            if (depth >= dims.Count)
            {
                return new CycleTreeResponse { Nodes = new List<CycleTreeNode>(), Total = 0, PageIndex = req.PageIndex, PageSize = req.PageSize };
            }

            var dimsPrefix = dims.Take(depth + 1).ToList();
            var groups = partRows
                .GroupBy(r => BuildKeyPath(dimsPrefix, (object)r), new KeyPathComparer())
                .Select(g => AggregateLeaf(dimsPrefix, g.Select(x => (object)x), categoryDictL, areaDictL))
                .ToList();

            // 标记是否还有下级
            foreach (var n in groups)
            {
                n.HasChildren = dimsPrefix.Count < dims.Count;
                n.Level = dimsPrefix.Count - 1;
                n.Children = null;
            }

            // 根层分页
            if (depth == 0)
            {
                var sorted = (req.Sort != null && req.Sort.Count > 0) ? ApplyRootSort(groups, req.Sort) : groups;
                var totalL = sorted.Count;
                var pageRoot = sorted.Skip((req.PageIndex - 1) * req.PageSize).Take(req.PageSize).ToList();
                return new CycleTreeResponse { Nodes = pageRoot, Total = totalL, PageIndex = req.PageIndex, PageSize = req.PageSize };
            }

            return new CycleTreeResponse { Nodes = groups, Total = groups.Count, PageIndex = 1, PageSize = groups.Count };
        }

        // 3) 提取叶子层所需字段到内存进行灵活分组（减少复杂 SQL 动态 GroupBy）
        var rows = await baseQuery.Select(p => new
        {
            p.Ch,
            p.Fd,
            p.ClassName,
            p.WorkAreaId,
            p.PlanStart,
            p.PlanEnd,
            p.ActualStart,
            p.ActualEnd
        }).ToListAsync();

        // 映射字典（名称）- 显式转 List 再 ToDictionary，避免泛型推断异常
        // 名称与排序字典（用于标签和排序）
        var categoryList = await _sqlSugarClient.Queryable<PlanType>()
            .OrderBy(x => x.OrderNo)
            .Select(x => new { x.Id, x.Name, x.OrderNo })
            .ToListAsync();
        var categoryDict = categoryList.ToDictionary(x => x.Id, x => x.Name);
        var categoryOrder = categoryList.ToDictionary(x => x.Id, x => x.OrderNo);
        var areaList = await _sqlSugarClient.Queryable<SysOrg>()
            .OrderBy(x => x.OrderNo)
            .Select(x => new { x.Id, x.Name, x.OrderNo })
            .ToListAsync();
        var areaDict = areaList.ToDictionary(x => x.Id, x => x.Name);
        var areaOrder = areaList.ToDictionary(x => x.Id, x => x.OrderNo);

        // 防御：空维度则默认以 ship 作为根
        // 已在前面定义 dims

        // 4) 计算叶子指标并按 dims 分组
        var leafGroups = rows
            .GroupBy(r => BuildKeyPath(dims, (object)r), new KeyPathComparer())
            .Select(g => AggregateLeaf(dims, g.Select(x => (object)x).ToList(), categoryDict, areaDict))
            .ToList();

        // 5) 构建树：按 dims 顺序 roll-up
        var fullTree = BuildTree(dims, leafGroups, categoryDict, areaDict, categoryOrder, areaOrder);

        // 6) 根层排序/分页
        var roots = fullTree;
        if (req.Sort != null && req.Sort.Count > 0)
        {
            roots = ApplyRootSort(roots, req.Sort);
        }
        else if (dims.Count > 0)
        {
            // 默认按首维度的自然顺序/OrderNo排序
            roots = roots.OrderBy(n => n, new CycleTreeNodeComparer(dims[0], categoryOrder, areaOrder)).ToList();
        }

        var total = roots.Count;
        var pageRoots = roots.Skip((req.PageIndex - 1) * req.PageSize).Take(req.PageSize).ToList();

        return new CycleTreeResponse
        {
            Nodes = pageRoots,
            Total = total,
            PageIndex = req.PageIndex,
            PageSize = req.PageSize
        };
    }

    /// <summary>
    /// 周期分析 - 统计聚合（根层聚合，支持路径定向聚合）
    /// </summary>
    [DisplayName("周期分析-统计聚合")]
    [ApiDescriptionSettings(Name = "cycleSummary")]
    [HttpPost("cycleSummary")]
    public async Task<CycleSummaryResponse> GetCycleSummary(CycleSummaryRequest req)
    {
        var dims = (req.Dims?.Count > 0) ? req.Dims : new List<string> { "ship" };
        var (startDate, endDate) = GetRangeOrWorkTime(req.Filters.RangeStart, req.Filters.RangeEnd, req.Filters.WorkTime);

        // 基础查询：与树形口径对齐
        var q = _sqlSugarClient.Queryable<PlanList>()
            .WhereIF(req.Filters.ClassName.HasValue, p => p.ClassName == req.Filters.ClassName)
            .WhereIF(req.Filters.WorkAreaId.HasValue, p => p.WorkAreaId == req.Filters.WorkAreaId)
            .WhereIF(!string.IsNullOrWhiteSpace(req.Filters.Keyword), p =>
                (req.Filters.ShipNos == null || req.Filters.ShipNos.Count == 0) &&
                (req.Filters.Sections == null || req.Filters.Sections.Count == 0) &&
                (
                    SqlFunc.Contains(SqlFunc.IsNull(p.Team, ""), req.Filters.Keyword!.Trim()) ||
                    SqlFunc.Contains(SqlFunc.IsNull(p.Remark, ""), req.Filters.Keyword!.Trim())
                ))
            .WhereIF(req.Filters.OnlyCompleted == true, p => p.ActualEnd != null)
            .ClearFilter<IOrgIdFilter>();

        if (req.Filters.ShipNos != null && req.Filters.ShipNos.Count > 0)
        {
            var keys = req.Filters.ShipNos.Where(s => !string.IsNullOrWhiteSpace(s)).Select(s => s.Trim()).ToList();
            if (keys.Count > 0)
            {
                var exp = Expressionable.Create<PlanList>();
                foreach (var k in keys) exp = exp.Or(p => SqlFunc.Contains(p.Ch, k));
                q = q.Where(exp.ToExpression());
            }
        }
        if (req.Filters.Sections != null && req.Filters.Sections.Count > 0)
        {
            var keys = req.Filters.Sections.Where(s => !string.IsNullOrWhiteSpace(s)).Select(s => s.Trim()).ToList();
            if (keys.Count > 0)
            {
                var exp = Expressionable.Create<PlanList>();
                foreach (var k in keys) exp = exp.Or(p => SqlFunc.Contains(p.Fd, k));
                q = q.Where(exp.ToExpression());
            }
        }

        q = ApplyTimeFilter(q, req.TimeField, req.TimeMatch, startDate, endDate);

        // 仅取必要字段
        var list = await q.Select(p => new
        {
            p.Id,
            p.Ch,
            p.Fd,
            p.ClassName,
            p.WorkAreaId,
            p.PlanStart,
            p.PlanEnd,
            p.ActualStart,
            p.ActualEnd
        }).ToListAsync();

        // 名称字典（用于分类/区域友好名）
        var categoryList = await _sqlSugarClient.Queryable<PlanType>().Select(x => new { x.Id, x.Name }).ToListAsync();
        var categoryDict = categoryList.ToDictionary(x => x.Id, x => x.Name);
        var areaList = await _sqlSugarClient.Queryable<SysOrg>().Select(x => new { x.Id, x.Name }).ToListAsync();
        var areaDict = areaList.ToDictionary(x => x.Id, x => x.Name);

        // 分组：目标路径优先（用于单行详情）
        IEnumerable<IGrouping<List<string>, object>> grouped;
        if (req.TargetKeyPath != null && req.TargetKeyPath.Count > 0)
        {
            grouped = list
                .Where(r =>
                {
                    var kp = BuildKeyPath(dims, r);
                    if (kp.Count < req.TargetKeyPath!.Count) return false;
                    for (int i = 0; i < req.TargetKeyPath!.Count; i++) if (kp[i] != req.TargetKeyPath![i]) return false;
                    return true;
                })
                .GroupBy(r => BuildKeyPath(dims, r), new KeyPathComparer());
        }
        else
        {
            grouped = list.GroupBy(r => BuildKeyPath(dims, r), new KeyPathComparer());
        }

        var rows = new List<CycleSummaryRow>();
        foreach (var g in grouped)
        {
            var row = ComputeSummaryRow(dims, g.Key, g.Select(x => (object)x), req.Filters.IgnoreNegative == true);
            // 友好显示名：将 keyPath 中的 category/region id 替换为名称
            var labels = BuildLabelsFromKeyPath(row.KeyPath, dims, categoryDict, areaDict);
            var displayPath = new List<string>();
            foreach (var d in dims)
            {
                if (labels.TryGetValue(d, out var v) && !string.IsNullOrEmpty(v)) displayPath.Add(v!);
                else displayPath.Add(string.Empty);
            }
            row.DisplayKeyPath = displayPath;
            row.DisplayLabel = (dims.Count > 0 && labels.TryGetValue(dims.Last(), out var lastV)) ? lastV : row.Label;
            row.Label = row.DisplayLabel ?? row.Label;
            rows.Add(row);
        }

        // 根层排序：沿用维度自然口径
        if (rows.Count > 1)
        {
            rows = rows.OrderBy(r => r.KeyPath, new KeyPathOrderComparer(dims, null, null)).ToList();
        }

        // 分页（仅根层；目标路径时直接返回全部匹配行）
        int total = rows.Count;
        if (req.TargetKeyPath == null || req.TargetKeyPath.Count == 0)
        {
            rows = rows.Skip((req.PageIndex - 1) * req.PageSize).Take(req.PageSize).ToList();
        }

        return new CycleSummaryResponse
        {
            Rows = rows,
            Total = total,
            PageIndex = req.PageIndex,
            PageSize = req.PageSize
        };
    }

    /// <summary>
    /// 统计聚合内部计算（旧甘特相关实现已移除）
    /// </summary>
    private static CycleSummaryRow ComputeSummaryRow(List<string> dims, List<string> keyPath, IEnumerable<object> items, bool ignoreNegative)
    {
        DateTime? minPlanStart = null, maxPlanEnd = null, minActualStart = null, maxActualEnd = null;
        int itemCount = 0, completed = 0, inProgress = 0;
        double planSum = 0, actualSum = 0; int planCnt = 0, actualCnt = 0;

        // 收集有效实际区间
        var intervals = new List<(DateTime start, DateTime end)>();
        foreach (var o in items)
        {
            dynamic r = o;
            itemCount++;
            if (r.ActualEnd != null) completed++;
            if (r.ActualStart != null && r.ActualEnd == null) inProgress++;

            if (r.PlanStart != null && r.PlanEnd != null)
            {
                if (!ignoreNegative || r.PlanEnd >= r.PlanStart)
                {
                    planSum += ((DateTime)r.PlanEnd - (DateTime)r.PlanStart).TotalDays;
                    planCnt++;
                    minPlanStart = MinDate(minPlanStart, r.PlanStart);
                    maxPlanEnd = MaxDate(maxPlanEnd, r.PlanEnd);
                }
            }
            if (r.ActualStart != null && r.ActualEnd != null)
            {
                if (!ignoreNegative || r.ActualEnd >= r.ActualStart)
                {
                    var s = (DateTime)r.ActualStart;
                    var e = (DateTime)r.ActualEnd;
                    if (e > s) intervals.Add((s, e));
                    actualSum += (e - s).TotalDays;
                    actualCnt++;
                    minActualStart = MinDate(minActualStart, r.ActualStart);
                    maxActualEnd = MaxDate(maxActualEnd, r.ActualEnd);
                }
            }
        }

        // 合并实际区间，计算工作与空挡
        intervals.Sort((a, b) => a.start.CompareTo(b.start));
        double workDays = 0, idleDays = 0;
        if (intervals.Count > 0)
        {
            var cur = intervals[0];
            for (int i = 1; i < intervals.Count; i++)
            {
                var nxt = intervals[i];
                if (nxt.start <= cur.end)
                {
                    if (nxt.end > cur.end) cur.end = nxt.end;
                }
                else
                {
                    // 空挡
                    idleDays += (nxt.start - cur.end).TotalDays;
                    workDays += (cur.end - cur.start).TotalDays;
                    cur = nxt;
                }
            }
            workDays += (cur.end - cur.start).TotalDays;
        }

        double? fullCycle = null;
        if (minActualStart != null && maxActualEnd != null)
            fullCycle = (maxActualEnd - minActualStart)?.TotalDays;
        else if (minPlanStart != null && maxPlanEnd != null)
            fullCycle = (maxPlanEnd - minPlanStart)?.TotalDays;

        return new CycleSummaryRow
        {
            Id = string.Join("|", keyPath),
            KeyPath = keyPath,
            Label = keyPath.Count > 0 ? keyPath.Last() : string.Empty,
            ItemCount = itemCount,
            CompletedCount = completed,
            InProgressCount = inProgress,
            ActualWorkDays = intervals.Count > 0 ? Math.Round(workDays, 2) : null,
            IdleGapDays = intervals.Count > 0 ? Math.Round(idleDays, 2) : null,
            FullCycleDays = fullCycle != null ? Math.Round(fullCycle.Value, 2) : null,
            PlanSpanDays = (minPlanStart != null && maxPlanEnd != null) ? Math.Round(((maxPlanEnd - minPlanStart)!.Value.TotalDays), 2) : null,
            ActualSpanDays = (minActualStart != null && maxActualEnd != null) ? Math.Round(((maxActualEnd - minActualStart)!.Value.TotalDays), 2) : null,
            PlanCycleAvgDays = planCnt > 0 ? Math.Round(planSum / planCnt, 2) : null,
            ActualCycleAvgDays = actualCnt > 0 ? Math.Round(actualSum / actualCnt, 2) : null,
            PlanCycleSumDays = planCnt > 0 ? Math.Round(planSum, 2) : null,
            ActualCycleSumDays = actualCnt > 0 ? Math.Round(actualSum, 2) : null
        };
    }

    // 甘特明细与旧实现已移除

    /// <summary>
    /// 周期分析（透视）
    /// </summary>
    [DisplayName("周期分析-透视")]
    [ApiDescriptionSettings(Name = "cyclePivot")]
    [HttpPost]
    public async Task<CyclePivotResponse> GetCyclePivot(CyclePivotRequest req)
    {
        var (startDate, endDate) = GetRangeOrWorkTime(req.Filters.RangeStart, req.Filters.RangeEnd, req.Filters.WorkTime);

        var baseQuery = _sqlSugarClient.Queryable<PlanList>()
            .WhereIF(req.Filters.ClassName.HasValue, p => p.ClassName == req.Filters.ClassName)
            .WhereIF(req.Filters.WorkAreaId.HasValue, p => p.WorkAreaId == req.Filters.WorkAreaId)
            // 关键词仅在未显式选中船号/分段时，对 Team/Remark 做模糊
            .WhereIF(!string.IsNullOrWhiteSpace(req.Filters.Keyword), p =>
                (req.Filters.ShipNos == null || req.Filters.ShipNos.Count == 0) &&
                (req.Filters.Sections == null || req.Filters.Sections.Count == 0) &&
                (
                    SqlFunc.Contains(SqlFunc.IsNull(p.Team, ""), req.Filters.Keyword!.Trim()) ||
                    SqlFunc.Contains(SqlFunc.IsNull(p.Remark, ""), req.Filters.Keyword!.Trim())
                ))
            .WhereIF(req.Filters.OnlyCompleted == true, p => p.ActualEnd != null)
            .ClearFilter<IOrgIdFilter>();

        baseQuery = ApplyTimeFilter(baseQuery, req.TimeField, req.TimeMatch, startDate, endDate);
        if (req.Filters.IgnoreNegative != false)
        {
            baseQuery = baseQuery.Where(p =>
                (p.PlanStart == null || p.PlanEnd == null || p.PlanStart <= p.PlanEnd) &&
                (p.ActualStart == null || p.ActualEnd == null || p.ActualStart <= p.ActualEnd));
        }

        var rows = await baseQuery.Select(p => new
        {
            p.Ch,
            p.Fd,
            p.ClassName,
            p.WorkAreaId,
            p.PlanStart,
            p.PlanEnd,
            p.ActualStart,
            p.ActualEnd
        }).ToListAsync();

        var categoryList = await _sqlSugarClient.Queryable<PlanType>()
            .OrderBy(x => x.OrderNo)
            .Select(x => new { x.Id, x.Name, x.OrderNo })
            .ToListAsync();
        var categoryDict = categoryList.ToDictionary(x => x.Id, x => x.Name);
        var categoryOrder = categoryList.ToDictionary(x => x.Id, x => x.OrderNo);
        var areaList = await _sqlSugarClient.Queryable<SysOrg>()
            .OrderBy(x => x.OrderNo)
            .Select(x => new { x.Id, x.Name, x.OrderNo })
            .ToListAsync();
        var areaDict = areaList.ToDictionary(x => x.Id, x => x.Name);
        var areaOrder = areaList.ToDictionary(x => x.Id, x => x.OrderNo);

        var rowDims = (req.RowDims != null && req.RowDims.Count > 0) ? req.RowDims : new List<string> { "ship" };
        var colDims = (req.ColDims != null && req.ColDims.Count > 0) ? req.ColDims : new List<string>();

        // 构造所有组合
        var leafGroups = rows
            .GroupBy(r => new
            {
                RowKey = BuildKeyPath(rowDims, (object)r),
                ColKey = BuildKeyPath(colDims, (object)r)
            })
            .Select(g => new
            {
                RowKey = g.Key.RowKey,
                ColKey = g.Key.ColKey,
                Agg = AggregateLeaf(rowDims.Concat(colDims).ToList(), g.Select(x => (object)x).ToList(), categoryDict, areaDict)
            }).ToList();

        // 行列头去重并稳定排序
        var rowKeys = leafGroups.Select(x => x.RowKey)
            .Distinct(new KeyPathComparer())
            .OrderBy(x => x, new KeyPathOrderComparer(rowDims, categoryOrder, areaOrder))
            .ToList();
        var colKeys = leafGroups.Select(x => x.ColKey)
            .Distinct(new KeyPathComparer())
            .OrderBy(x => x, new KeyPathOrderComparer(colDims, categoryOrder, areaOrder))
            .ToList();

        var rowsHeader = rowKeys.Select(k => new PivotHeader { KeyPath = k, Label = k.LastOrDefault() ?? "", Level = k.Count - 1 }).ToList();
        var colsHeader = colKeys.Select(k => new PivotHeader { KeyPath = k, Label = k.LastOrDefault() ?? "", Level = k.Count - 1 }).ToList();

        // 建立索引
        var rowIndex = rowKeys.Select((k, i) => (k, i)).ToDictionary(t => string.Join("|", t.k), t => t.i);
        var colIndex = colKeys.Select((k, i) => (k, i)).ToDictionary(t => string.Join("|", t.k), t => t.i);

        var cells = new List<PivotCell>();
        foreach (var g in leafGroups)
        {
            var rKey = string.Join("|", g.RowKey);
            var cKey = string.Join("|", g.ColKey);
            var r = rowIndex[rKey];
            var c = colIndex[cKey];
            cells.Add(new PivotCell
            {
                R = r,
                C = c,
                ItemCount = g.Agg.ItemCount,
                PlanCycleAvgDays = g.Agg.PlanCycleAvgDays,
                ActualCycleAvgDays = g.Agg.ActualCycleAvgDays,
                PlanCycleSumDays = g.Agg.PlanCycleSumDays,
                ActualCycleSumDays = g.Agg.ActualCycleSumDays,
                VarianceSumDays = g.Agg.VarianceSumDays,
                VarianceAvgDays = g.Agg.VarianceAvgDays
            });
        }

        return new CyclePivotResponse
        {
            Rows = rowsHeader,
            Cols = colsHeader,
            Cells = cells
        };
    }

    /// <summary>
    /// 周期分析导出（树形当前层）
    /// </summary>
    [DisplayName("周期分析-导出树形")]
    [ApiDescriptionSettings(Name = "exportCycleTreeExcel")]
    [HttpPost, NonUnify]
    public async Task<IActionResult> ExportCycleTreeExcel(CycleTreeExportRequest req)
    {
        // 复用树形接口的查询逻辑，但导出仅导出“当前层”数据；ExportAll=true 忽略分页
        var (startDate, endDate) = GetRangeOrWorkTime(req.Filters.RangeStart, req.Filters.RangeEnd, req.Filters.WorkTime);

        var baseQuery = _sqlSugarClient.Queryable<PlanList>()
            .WhereIF(req.Filters.ClassName.HasValue, p => p.ClassName == req.Filters.ClassName)
            .WhereIF(req.Filters.WorkAreaId.HasValue, p => p.WorkAreaId == req.Filters.WorkAreaId)
            // 关键词仅在未显式选中船号/分段时，对 Team/Remark 做模糊
            .WhereIF(!string.IsNullOrWhiteSpace(req.Filters.Keyword), p =>
                (req.Filters.ShipNos == null || req.Filters.ShipNos.Count == 0) &&
                (req.Filters.Sections == null || req.Filters.Sections.Count == 0) &&
                (
                    SqlFunc.Contains(SqlFunc.IsNull(p.Team, ""), req.Filters.Keyword!.Trim()) ||
                    SqlFunc.Contains(SqlFunc.IsNull(p.Remark, ""), req.Filters.Keyword!.Trim())
                ))
            .WhereIF(req.Filters.OnlyCompleted == true, p => p.ActualEnd != null)
            .ClearFilter<IOrgIdFilter>();

        // 船号/分段模糊匹配（支持多关键词 OR）
        if (req.Filters.ShipNos != null && req.Filters.ShipNos.Count > 0)
        {
            var keys = req.Filters.ShipNos.Where(s => !string.IsNullOrWhiteSpace(s)).Select(s => s.Trim()).ToList();
            if (keys.Count > 0)
            {
                var exp = Expressionable.Create<PlanList>();
                foreach (var k in keys) exp = exp.Or(p => SqlFunc.Contains(p.Ch, k));
                baseQuery = baseQuery.Where(exp.ToExpression());
            }
        }
        if (req.Filters.Sections != null && req.Filters.Sections.Count > 0)
        {
            var keys = req.Filters.Sections.Where(s => !string.IsNullOrWhiteSpace(s)).Select(s => s.Trim()).ToList();
            if (keys.Count > 0)
            {
                var exp = Expressionable.Create<PlanList>();
                foreach (var k in keys) exp = exp.Or(p => SqlFunc.Contains(p.Fd, k));
                baseQuery = baseQuery.Where(exp.ToExpression());
            }
        }

        baseQuery = ApplyTimeFilter(baseQuery, req.TimeField, req.TimeMatch, startDate, endDate);
        if (req.Filters.IgnoreNegative != false)
        {
            baseQuery = baseQuery.Where(p =>
                (p.PlanStart == null || p.PlanEnd == null || p.PlanStart <= p.PlanEnd) &&
                (p.ActualStart == null || p.ActualEnd == null || p.ActualStart <= p.ActualEnd));
        }

        var dims = (req.Dims != null && req.Dims.Count > 0) ? req.Dims : new List<string> { "ship" };
        var filtered = ApplyPrefixFilters(baseQuery, dims, req.ParentKeyPath);
        var rows = await filtered.Select(p => new
        {
            p.Ch,
            p.Fd,
            p.ClassName,
            p.WorkAreaId,
            p.PlanStart,
            p.PlanEnd,
            p.ActualStart,
            p.ActualEnd
        }).ToListAsync();

        var categoryList = await _sqlSugarClient.Queryable<PlanType>()
            .OrderBy(x => x.OrderNo)
            .Select(x => new { x.Id, x.Name, x.OrderNo })
            .ToListAsync();
        var categoryDict = categoryList.ToDictionary(x => x.Id, x => x.Name);
        var categoryOrder = categoryList.ToDictionary(x => x.Id, x => x.OrderNo);
        var areaList = await _sqlSugarClient.Queryable<SysOrg>()
            .OrderBy(x => x.OrderNo)
            .Select(x => new { x.Id, x.Name, x.OrderNo })
            .ToListAsync();
        var areaDict = areaList.ToDictionary(x => x.Id, x => x.Name);
        var areaOrder = areaList.ToDictionary(x => x.Id, x => x.OrderNo);

        var depth = req.ParentKeyPath?.Count ?? 0;

        // 导出全部层级：构造完整树并拍平
        if (req.ExportAllLevels)
        {
            var leafGroupsAll = rows
                .GroupBy(r => BuildKeyPath(dims, (object)r), new KeyPathComparer())
                .Select(g => AggregateLeaf(dims, g.Select(x => (object)x), categoryDict, areaDict))
                .ToList();

            var tree = BuildTree(dims, leafGroupsAll, categoryDict, areaDict, categoryOrder, areaOrder);
            // 如果有父路径，先定位子树
            if (depth > 0 && req.ParentKeyPath != null && req.ParentKeyPath.Count > 0)
            {
                var prefix = string.Join("|", req.ParentKeyPath);
                tree = tree.FirstOrDefault(n => n.Id == prefix)?.Children ?? new List<CycleTreeNode>();
            }

            var flat = new List<CycleTreeExportRow>();
            FlattenTreeToRows(tree, flat);
            return ExcelHelper.ExportTemplate(flat, "周期分析-全层");
        }
        else
        {
            // 当前层导出
            var dimsPrefix = dims.Take(depth + 1).ToList();
            var groups = rows
                .GroupBy(r => BuildKeyPath(dimsPrefix, (object)r), new KeyPathComparer())
                .Select(g => AggregateLeaf(dimsPrefix, g.Select(x => (object)x), categoryDict, areaDict))
                .ToList();

            // 排序和分页（导出全部忽略分页）
            var sorted = (req.Sort != null && req.Sort.Count > 0) ? ApplyRootSort(groups, req.Sort) : groups;
            var exportList = (req.ExportAll ? sorted : sorted.Skip((req.PageIndex - 1) * req.PageSize).Take(req.PageSize)).ToList();

            var output = exportList.Select(NodeToRow).ToList();
            return ExcelHelper.ExportTemplate(output, "周期分析-当前层");
        }
    }

    // ========= 私有辅助 =========

    private static (DateTime startDate, DateTime endDate) GetRangeOrWorkTime(DateTime? start, DateTime? end, string? workTime)
    {
        if (start.HasValue && end.HasValue) return (start.Value, end.Value);
        workTime = workTime?.Trim();
        var (s, e) = DateTimeUtil.CalculateWorkTimeRange(workTime ?? DateTime.Now.ToString("yyyy-MM"));
        return (s, e);
    }

    private static ISugarQueryable<PlanList> ApplyTimeFilter(ISugarQueryable<PlanList> q, TimeFieldMode field, TimeMatchMode match, DateTime start, DateTime end)
    {
        // 直接用 Where 表达式，避免 Expressionable 泛型推断问题
        if (field == TimeFieldMode.Plan)
        {
            if (match == TimeMatchMode.Intersects)
                return q.Where(p => p.PlanStart != null && p.PlanEnd != null && p.PlanStart <= end && p.PlanEnd >= start);
            if (match == TimeMatchMode.StartWithin)
                return q.Where(p => p.PlanStart != null && p.PlanStart >= start && p.PlanStart <= end);
            // EndWithin
            return q.Where(p => p.PlanEnd != null && p.PlanEnd >= start && p.PlanEnd <= end);
        }
        else if (field == TimeFieldMode.Actual)
        {
            if (match == TimeMatchMode.Intersects)
                return q.Where(p => p.ActualStart != null && p.ActualEnd != null && p.ActualStart <= end && p.ActualEnd >= start);
            if (match == TimeMatchMode.StartWithin)
                return q.Where(p => p.ActualStart != null && p.ActualStart >= start && p.ActualStart <= end);
            // EndWithin
            return q.Where(p => p.ActualEnd != null && p.ActualEnd >= start && p.ActualEnd <= end);
        }
        else
        {
            if (match == TimeMatchMode.Intersects)
                return q.Where(p =>
                    (p.PlanStart != null && p.PlanEnd != null && p.PlanStart <= end && p.PlanEnd >= start) ||
                    (p.ActualStart != null && p.ActualEnd != null && p.ActualStart <= end && p.ActualEnd >= start));
            if (match == TimeMatchMode.StartWithin)
                return q.Where(p =>
                    (p.PlanStart != null && p.PlanStart >= start && p.PlanStart <= end) ||
                    (p.ActualStart != null && p.ActualStart >= start && p.ActualStart <= end));
            // EndWithin
            return q.Where(p =>
                (p.PlanEnd != null && p.PlanEnd >= start && p.PlanEnd <= end) ||
                (p.ActualEnd != null && p.ActualEnd >= start && p.ActualEnd <= end));
        }
    }

    private static List<string> BuildKeyPath(List<string> dims, object r)
    {
        dynamic dr = r;
        var list = new List<string>();
        foreach (var d in dims)
        {
            switch (d)
            {
                case "ship": list.Add((string)(dr.Ch ?? "")); break;
                case "section": list.Add((string)(dr.Fd ?? "")); break;
                case "sectionFirst":
                    {
                        string fd = (string)(dr.Fd ?? "");
                        list.Add(!string.IsNullOrEmpty(fd) ? fd.Substring(0, 1) : "");
                    }
                    break;
                case "category": list.Add(dr.ClassName != null ? ((object)dr.ClassName).ToString() : ""); break;
                case "region": list.Add(dr.WorkAreaId != null ? ((object)dr.WorkAreaId).ToString() : ""); break;
                default: list.Add(""); break;
            }
        }
        return list;
    }

    private static List<GanttHierarchyNode> BuildGanttHierarchy(List<dynamic> rows, List<string> dims, Dictionary<long, string> categoryDict, Dictionary<long, string> areaDict, bool onlyDelayed, out DateTime? minTime, out DateTime? maxTime, out int totalItems)
    {
        minTime = null;
        maxTime = null;
        totalItems = 0;

        var root = new GanttHierarchyNode
        {
            Id = "root",
            Label = "root",
            Level = -1,
            KeyPath = new List<string>(),
            DisplayPath = new List<string>(),
        };

        foreach (var row in rows)
        {
            var keyPath = BuildKeyPath(dims, row);
            var displaySegments = BuildDisplaySegments(dims, row, categoryDict, areaDict);
            var item = CreateGanttItem(row, keyPath);
            if (item == null) continue;
            if (onlyDelayed && item.Status != "delayed") continue;

            UpdateTimeRange(item, ref minTime, ref maxTime);
            totalItems++;

            InsertIntoHierarchy(root, keyPath, displaySegments, item);
        }

        ClearRuntimeFields(root.Children);
        return root.Children;
    }

    private static (List<GanttHierarchyNode> Nodes, int TotalNodes, int TotalItems, DateTime? MinTime, DateTime? MaxTime) BuildLazyGanttNodes(
        List<dynamic> rows,
        List<string> dims,
        List<string>? parentKeyPath,
        int pageIndex,
        int pageSize,
        Dictionary<long, string> categoryDict,
        Dictionary<long, string> areaDict)
    {
        var nodes = new List<GanttHierarchyNode>();
        DateTime? minTime = null;
        DateTime? maxTime = null;
        var totalItems = rows.Count;
        var depth = parentKeyPath?.Count ?? 0;
        if (dims.Count == 0)
        {
            return (nodes, 0, totalItems, minTime, maxTime);
        }
        var dimsPrefix = dims.Take(Math.Min(depth + 1, dims.Count)).ToList();
        var groups = rows
            .GroupBy(r => BuildKeyPath(dimsPrefix, (object)r), new KeyPathComparer())
            .ToList();

        foreach (var group in groups)
        {
            var node = AggregateGanttNode(dimsPrefix, dims, group.Select(x => (object)x), categoryDict, areaDict);
            UpdateRangeFromNode(node, ref minTime, ref maxTime);
            nodes.Add(node);
        }

        nodes = nodes
            .OrderBy(n => string.Join("/", n.KeyPath))
            .ToList();

        var totalNodes = nodes.Count;
        if (depth == 0 && totalNodes > 0)
        {
            var currentPage = Math.Max(pageIndex, 1);
            var size = Math.Max(pageSize, 1);
            nodes = nodes.Skip((currentPage - 1) * size).Take(size).ToList();
        }

        return (nodes, totalNodes, totalItems, minTime, maxTime);
    }

    private static GanttHierarchyNode AggregateGanttNode(
        List<string> dimsPrefix,
        List<string> fullDims,
        IEnumerable<object> rows,
        Dictionary<long, string> categoryDict,
        Dictionary<long, string> areaDict)
    {
        var list = rows.ToList();
        if (list.Count == 0)
        {
            return new GanttHierarchyNode();
        }

        dynamic first = list.First();
        List<string> keyPath = BuildKeyPath(dimsPrefix, first);
        List<string> displayPath = BuildDisplaySegments(dimsPrefix, first, categoryDict, areaDict);
        DateTime? planStart = null;
        DateTime? planEnd = null;
        DateTime? actualStart = null;
        DateTime? actualEnd = null;

        foreach (dynamic row in list)
        {
            planStart = MinDate(planStart, row.PlanStart);
            planEnd = MaxDate(planEnd, row.PlanEnd);
            actualStart = MinDate(actualStart, row.ActualStart);
            actualEnd = MaxDate(actualEnd, row.ActualEnd);
        }

        return new GanttHierarchyNode
        {
            Id = keyPath.Count > 0 ? string.Join("/", keyPath) : Guid.NewGuid().ToString(),
            Label = displayPath.LastOrDefault(s => !string.IsNullOrWhiteSpace(s)) ?? keyPath.LastOrDefault() ?? "(未命名)",
            Level = Math.Max(dimsPrefix.Count - 1, 0),
            KeyPath = keyPath,
            DisplayPath = displayPath,
            PlanStart = planStart,
            PlanEnd = planEnd,
            ActualStart = actualStart,
            ActualEnd = actualEnd,
            HasChildren = dimsPrefix.Count < fullDims.Count,
            Children = new List<GanttHierarchyNode>(),
            Items = new List<GanttItem>(),
            ChildrenLookup = new Dictionary<string, GanttHierarchyNode>()
        };
    }

    private static void UpdateRangeFromNode(GanttHierarchyNode node, ref DateTime? minTime, ref DateTime? maxTime)
    {
        foreach (var dt in new[] { node.PlanStart, node.PlanEnd, node.ActualStart, node.ActualEnd })
        {
            if (!dt.HasValue) continue;
            if (!minTime.HasValue || dt.Value < minTime.Value) minTime = dt;
            if (!maxTime.HasValue || dt.Value > maxTime.Value) maxTime = dt;
        }
    }

    private static void InsertIntoHierarchy(GanttHierarchyNode root, List<string> keyPath, List<string> displaySegments, GanttItem item)
    {
        var parent = root;
        for (int level = 0; level < keyPath.Count; level++)
        {
            var rawValue = keyPath[level] ?? string.Empty;
            var display = level < displaySegments.Count ? displaySegments[level] : rawValue;
            parent = GetOrCreateChild(parent, level, rawValue, display);
            AccumulateNodeMetrics(parent, item);
        }

        if (parent.Items == null) parent.Items = new List<GanttItem>();
        parent.Items.Add(item);
    }

    private static GanttHierarchyNode GetOrCreateChild(GanttHierarchyNode parent, int level, string key, string label)
    {
        parent.ChildrenLookup ??= new Dictionary<string, GanttHierarchyNode>();
        var lookupKey = $"{level}:{key}";
        if (!parent.ChildrenLookup.TryGetValue(lookupKey, out var child))
        {
            var keyPath = parent.KeyPath.Concat(new[] { key }).ToList();
            var displayPath = parent.DisplayPath.Concat(new[] { label }).ToList();
            child = new GanttHierarchyNode
            {
                Id = keyPath.Count > 0 ? string.Join("/", keyPath) : Guid.NewGuid().ToString(),
                Label = string.IsNullOrWhiteSpace(label) ? "(未分组)" : label,
                Level = level,
                KeyPath = keyPath,
                DisplayPath = displayPath,
            };
            parent.ChildrenLookup[lookupKey] = child;
            parent.Children.Add(child);
        }
        return child;
    }

    private static void AccumulateNodeMetrics(GanttHierarchyNode node, GanttItem item)
    {
        node.PlanStart = MinDate(node.PlanStart, item.PlanStart);
        node.PlanEnd = MaxDate(node.PlanEnd, item.PlanEnd);
        node.ActualStart = MinDate(node.ActualStart, item.ActualStart);
        node.ActualEnd = MaxDate(node.ActualEnd, item.ActualEnd);

        if (item.Progress.HasValue)
        {
            node.ProgressSum += (double)item.Progress.Value;
            node.ProgressCount += 1;
            node.ProgressPercent = Math.Round(node.ProgressSum / node.ProgressCount, 2);
        }
    }

    private static void UpdateTimeRange(GanttItem item, ref DateTime? minTime, ref DateTime? maxTime)
    {
        foreach (var dt in new[] { item.PlanStart, item.PlanEnd, item.ActualStart, item.ActualEnd })
        {
            if (!dt.HasValue) continue;
            if (!minTime.HasValue || dt.Value < minTime.Value) minTime = dt;
            if (!maxTime.HasValue || dt.Value > maxTime.Value) maxTime = dt;
        }
    }

    private static void ClearRuntimeFields(List<GanttHierarchyNode> nodes)
    {
        foreach (var node in nodes)
        {
            node.HasChildren = node.Children.Count > 0;
            node.ChildrenLookup.Clear();
            if (node.Children.Count > 0) ClearRuntimeFields(node.Children);
        }
    }

    private static List<string> BuildDisplaySegments(List<string> dims, dynamic row, Dictionary<long, string> categoryDict, Dictionary<long, string> areaDict)
    {
        var segments = new List<string>();
        foreach (var dim in dims)
        {
            switch (dim)
            {
                case "ship":
                    segments.Add((string)(row.Ch ?? string.Empty));
                    break;
                case "section":
                    segments.Add((string)(row.Fd ?? string.Empty));
                    break;
                case "sectionFirst":
                    {
                        string fd = (string)(row.Fd ?? string.Empty);
                        segments.Add(!string.IsNullOrEmpty(fd) ? fd.Substring(0, 1) : string.Empty);
                    }
                    break;
                case "category":
                    {
                        if (row.ClassName == null)
                        {
                            segments.Add(string.Empty);
                        }
                        else
                        {
                            var cid = (long)row.ClassName;
                            segments.Add(categoryDict.TryGetValue(cid, out var cn) ? cn : cid.ToString());
                        }
                    }
                    break;
                case "region":
                    {
                        if (row.WorkAreaId == null)
                        {
                            segments.Add(string.Empty);
                        }
                        else
                        {
                            var aid = (long)row.WorkAreaId;
                            segments.Add(areaDict.TryGetValue(aid, out var an) ? an : aid.ToString());
                        }
                    }
                    break;
                default:
                    segments.Add(string.Empty);
                    break;
            }
        }
        return segments;
    }

    private static GanttItem? CreateGanttItem(dynamic row, List<string> keyPath)
    {
        var labelParts = new List<string>();
        var ship = (string)(row.Ch ?? string.Empty);
        var section = (string)(row.Fd ?? string.Empty);
        var team = (string)(row.Team ?? string.Empty);
        if (!string.IsNullOrWhiteSpace(ship)) labelParts.Add(ship);
        if (!string.IsNullOrWhiteSpace(section)) labelParts.Add(section);
        if (!string.IsNullOrWhiteSpace(team)) labelParts.Add(team);

        var item = new GanttItem
        {
            Id = row.Id.ToString(),
            Label = labelParts.Count > 0 ? string.Join("-", labelParts) : row.Id.ToString(),
            KeyPath = keyPath,
            PlanStart = row.PlanStart,
            PlanEnd = row.PlanEnd,
            ActualStart = row.ActualStart,
            ActualEnd = row.ActualEnd,
        };

        if (item.ActualEnd.HasValue)
        {
            item.Status = (item.PlanEnd.HasValue && item.ActualEnd.Value.Date > item.PlanEnd.Value.Date) ? "delayed" : "onTime";
            item.Progress = 100m;
        }
        else if (item.ActualStart.HasValue)
        {
            item.Status = "inProgress";
            item.Progress = 50m;
        }
        else
        {
            item.Status = "notStarted";
            item.Progress = 0m;
        }

        return item;
    }

    private static List<CycleTreeNode> ApplyRootSort(List<CycleTreeNode> roots, List<SortSpec> sort)
    {
        // 简易单字段排序（多字段可扩展）
        var s = sort.First();
        Func<CycleTreeNode, object?> key = s.Field switch
        {
            nameof(CycleTreeNode.ItemCount) => n => n.ItemCount,
            nameof(CycleTreeNode.CompletedCount) => n => n.CompletedCount,
            nameof(CycleTreeNode.InProgressCount) => n => n.InProgressCount,
            nameof(CycleTreeNode.PlanCycleAvgDays) => n => n.PlanCycleAvgDays,
            nameof(CycleTreeNode.ActualCycleAvgDays) => n => n.ActualCycleAvgDays,
            nameof(CycleTreeNode.VarianceAvgDays) => n => n.VarianceAvgDays,
            nameof(CycleTreeNode.PlanSpanDays) => n => n.PlanSpanDays,
            nameof(CycleTreeNode.ActualSpanDays) => n => n.ActualSpanDays,
            _ => n => n.ItemCount
        };
        return s.Desc ? roots.OrderByDescending(key).ToList() : roots.OrderBy(key).ToList();
    }

    private static CycleTreeNode AggregateLeaf(List<string> dims, IEnumerable<object> list, Dictionary<long, string> categoryDict, Dictionary<long, string> areaDict)
    {
        // 维度标签
        var first = (dynamic)list.First();
        var labels = new Dictionary<string, string?>();
        foreach (var d in dims)
        {
            switch (d)
            {
                case "ship": labels["ship"] = first.Ch; break;
                case "section": labels["section"] = first.Fd; break;
                case "sectionFirst": labels["sectionFirst"] = !string.IsNullOrEmpty((string)first.Fd) ? ((string)first.Fd).Substring(0, 1) : null; break;
                case "category":
                    {
                        string? name = null;
                        if (first.ClassName != null)
                        {
                            var id = (long)first.ClassName;
                            if (categoryDict.TryGetValue(id, out var n)) name = n;
                            else name = id.ToString();
                        }
                        labels["category"] = name;
                    }
                    break;
                case "region":
                    {
                        string? name = null;
                        if (first.WorkAreaId != null)
                        {
                            var id = (long)first.WorkAreaId;
                            if (areaDict.TryGetValue(id, out var n)) name = n; else name = id.ToString();
                        }
                        labels["region"] = name;
                    }
                    break;
            }
        }

        // 指标累加
        int itemCount = 0, completed = 0, inProgress = 0;
        double planSum = 0, actualSum = 0; int planCnt = 0, actualCnt = 0;
        DateTime? minPlanStart = null, maxPlanEnd = null, minActualStart = null, maxActualEnd = null;

        foreach (var obj in list)
        {
            dynamic r = obj;
            itemCount++;
            if (r.ActualEnd != null) completed++;
            if (r.ActualStart != null && r.ActualEnd == null) inProgress++;

            if (r.PlanStart != null && r.PlanEnd != null)
            {
                planSum += (r.PlanEnd - r.PlanStart).TotalDays;
                planCnt++;
                minPlanStart = minPlanStart == null ? r.PlanStart : (r.PlanStart < minPlanStart ? r.PlanStart : minPlanStart);
                maxPlanEnd = maxPlanEnd == null ? r.PlanEnd : (r.PlanEnd > maxPlanEnd ? r.PlanEnd : maxPlanEnd);
            }
            if (r.ActualStart != null && r.ActualEnd != null)
            {
                actualSum += (r.ActualEnd - r.ActualStart).TotalDays;
                actualCnt++;
                minActualStart = minActualStart == null ? r.ActualStart : (r.ActualStart < minActualStart ? r.ActualStart : minActualStart);
                maxActualEnd = maxActualEnd == null ? r.ActualEnd : (r.ActualEnd > maxActualEnd ? r.ActualEnd : maxActualEnd);
            }
        }

        var node = new CycleTreeNode
        {
            KeyPath = BuildKeyPath(dims, (object)first),
            Id = string.Join("|", BuildKeyPath(dims, (object)first)),
            Level = dims.Count - 1,
            Labels = labels,
            ItemCount = itemCount,
            CompletedCount = completed,
            InProgressCount = inProgress,
            PlanCycleSumDays = planSum,
            PlanCycleCount = planCnt,
            ActualCycleSumDays = actualSum,
            ActualCycleCount = actualCnt,
            PlanCycleAvgDays = planCnt > 0 ? Math.Round(planSum / planCnt, 2) : null,
            ActualCycleAvgDays = actualCnt > 0 ? Math.Round(actualSum / actualCnt, 2) : null,
            VarianceAvgDays = (planCnt > 0 && actualCnt > 0) ? Math.Round((actualSum / actualCnt) - (planSum / planCnt), 2) : null,
            VarianceSumDays = (planCnt > 0 && actualCnt > 0) ? Math.Round(actualSum - planSum, 2) : (double?)null,
            MinPlanStart = minPlanStart,
            MaxPlanEnd = maxPlanEnd,
            MinActualStart = minActualStart,
            MaxActualEnd = maxActualEnd,
            PlanSpanDays = (minPlanStart != null && maxPlanEnd != null) ? (maxPlanEnd - minPlanStart)?.TotalDays : null,
            ActualSpanDays = (minActualStart != null && maxActualEnd != null) ? (maxActualEnd - minActualStart)?.TotalDays : null,
            HasChildren = false
        };
        return node;
    }

    private static List<CycleTreeNode> BuildTree(
        List<string> dims,
        List<CycleTreeNode> leaves,
        Dictionary<long,string> categoryDict,
        Dictionary<long,string> areaDict,
        Dictionary<long,int>? categoryOrder = null,
        Dictionary<long,int>? areaOrder = null)
    {
        if (dims.Count <= 1) return leaves; // 只有一层时直接返回（每个叶子即根）

        // 将叶子按路径升序，逐级汇总到父节点
        var levelCount = dims.Count;
        // 从底向上构建：先准备以 level-1 作为叶子，然后 roll-up 到 level-2 ... 0
        var byPath = leaves.ToDictionary(n => string.Join("|", n.KeyPath));

        // 生成父节点集合
        for (int lvl = levelCount - 2; lvl >= 0; lvl--)
        {
            var parentGroups = new Dictionary<string, CycleTreeNode>();

            foreach (var leaf in leaves)
            {
                if (leaf.KeyPath.Count <= lvl) continue;
                var parentKeyPath = leaf.KeyPath.Take(lvl + 1).ToList();
                var parentKey = string.Join("|", parentKeyPath);

                if (!parentGroups.TryGetValue(parentKey, out var parent))
                {
                    parent = new CycleTreeNode
                    {
                        KeyPath = parentKeyPath,
                        Id = parentKey,
                        Level = lvl,
                        Labels = BuildLabelsFromKeyPath(parentKeyPath, dims, categoryDict, areaDict),
                        Children = new List<CycleTreeNode>(),
                        HasChildren = true
                    };
                    parentGroups[parentKey] = parent;
                }

                // 聚合到父节点
                parent.ItemCount += leaf.ItemCount;
                parent.CompletedCount += leaf.CompletedCount;
                parent.InProgressCount += leaf.InProgressCount;
                parent.PlanCycleSumDays += leaf.PlanCycleSumDays;
                parent.PlanCycleCount += leaf.PlanCycleCount;
                parent.ActualCycleSumDays += leaf.ActualCycleSumDays;
                parent.ActualCycleCount += leaf.ActualCycleCount;

                parent.MinPlanStart = MinDate(parent.MinPlanStart, leaf.MinPlanStart);
                parent.MaxPlanEnd = MaxDate(parent.MaxPlanEnd, leaf.MaxPlanEnd);
                parent.MinActualStart = MinDate(parent.MinActualStart, leaf.MinActualStart);
                parent.MaxActualEnd = MaxDate(parent.MaxActualEnd, leaf.MaxActualEnd);
            }

            // 回填平均与跨度
            foreach (var parent in parentGroups.Values)
            {
                parent.PlanCycleAvgDays = parent.PlanCycleCount > 0 ? Math.Round(parent.PlanCycleSumDays / parent.PlanCycleCount, 2) : null;
                parent.ActualCycleAvgDays = parent.ActualCycleCount > 0 ? Math.Round(parent.ActualCycleSumDays / parent.ActualCycleCount, 2) : null;
                parent.VarianceAvgDays = (parent.PlanCycleAvgDays.HasValue && parent.ActualCycleAvgDays.HasValue)
                    ? Math.Round(parent.ActualCycleAvgDays.Value - parent.PlanCycleAvgDays.Value, 2) : null;
                parent.VarianceSumDays = (parent.ActualCycleSumDays > 0 || parent.PlanCycleSumDays > 0)
                    ? Math.Round(parent.ActualCycleSumDays - parent.PlanCycleSumDays, 2) : (double?)null;
                parent.PlanSpanDays = (parent.MinPlanStart != null && parent.MaxPlanEnd != null) ? (parent.MaxPlanEnd - parent.MinPlanStart)?.TotalDays : null;
                parent.ActualSpanDays = (parent.MinActualStart != null && parent.MaxActualEnd != null) ? (parent.MaxActualEnd - parent.MinActualStart)?.TotalDays : null;
            }

            // 建立父子关系
            foreach (var leaf in leaves)
            {
                if (leaf.KeyPath.Count <= lvl) continue;
                var parentKey = string.Join("|", leaf.KeyPath.Take(lvl + 1));
                var parent = parentGroups[parentKey];
                parent.Children!.Add(leaf);
            }

            // 更新当前层为父集合，继续向上
            // 对当前层父节点按维度排序：sectionFirst 按数字1-9，category/region按OrderNo，其它按字符串
            leaves = parentGroups.Values
                .OrderBy(n => n, new CycleTreeNodeComparer(dims[lvl], categoryOrder, areaOrder))
                .ToList();
        }

        // 现在 leaves 即为根集合
        return leaves;
    }

    private static Dictionary<string,string?> BuildLabelsFromKeyPath(List<string> keyPath, List<string> dims, Dictionary<long,string> categoryDict, Dictionary<long,string> areaDict)
    {
        var labels = new Dictionary<string,string?>();
        for (int i=0;i<keyPath.Count && i<dims.Count;i++)
        {
            var dim = dims[i]; var key = keyPath[i];
            switch (dim)
            {
                case "ship": labels["ship"] = key; break;
                case "section": labels["section"] = key; break;
                case "sectionFirst": labels["sectionFirst"] = key; break;
                case "category":
                    if (long.TryParse(key, out var cid) && categoryDict.TryGetValue(cid, out var cname)) labels["category"] = cname; else labels["category"] = key; break;
                case "region":
                    if (long.TryParse(key, out var aid) && areaDict.TryGetValue(aid, out var aname)) labels["region"] = aname; else labels["region"] = key; break;
            }
        }
        return labels;
    }

    // 根据父路径为查询添加前缀过滤，减少数据量
    private static ISugarQueryable<PlanList> ApplyPrefixFilters(ISugarQueryable<PlanList> q, List<string> dims, List<string>? parentKeyPath)
    {
        if (parentKeyPath == null || parentKeyPath.Count == 0) return q;
        for (int i = 0; i < parentKeyPath.Count && i < dims.Count; i++)
        {
            var dim = dims[i]; var key = parentKeyPath[i];
            switch (dim)
            {
                case "ship": q = q.Where(p => p.Ch == key); break;
                case "section": q = q.Where(p => p.Fd == key); break;
                case "sectionFirst": q = q.Where(p => p.Fd.StartsWith(key)); break;
                case "category":
                    if (long.TryParse(key, out var cid)) q = q.Where(p => p.ClassName == cid);
                    break;
                case "region":
                    if (long.TryParse(key, out var aid)) q = q.Where(p => p.WorkAreaId == aid);
                    break;
            }
        }
        return q;
    }

    private static DateTime? MinDate(DateTime? a, DateTime? b)
        => (a == null) ? b : (b == null ? a : (a <= b ? a : b));
    private static DateTime? MaxDate(DateTime? a, DateTime? b)
        => (a == null) ? b : (b == null ? a : (a >= b ? a : b));

    private sealed class KeyPathComparer : IEqualityComparer<List<string>>
    {
        public bool Equals(List<string>? x, List<string>? y)
        {
            if (ReferenceEquals(x, y)) return true;
            if (x == null || y == null || x.Count != y.Count) return false;
            for (int i = 0; i < x.Count; i++) if (x[i] != y[i]) return false; return true;
        }
        public int GetHashCode(List<string> obj)
        {
            unchecked
            {
                int hash = 17;
                foreach (var s in obj) hash = hash * 23 + (s?.GetHashCode() ?? 0);
                return hash;
            }
        }
    }

    /// <summary>
    /// 针对透视表头按维度自定义排序：
    /// - sectionFirst: 若是单字符数字，按数值1-9排序；非数字排在数字后，按字母排序
    /// - category/region: 尝试按Id的OrderNo排序；否则按字符串
    /// - 其它: 默认按字符串
    /// </summary>
    private sealed class KeyPathOrderComparer : IComparer<List<string>>
    {
        private readonly List<string> _dims;
        private readonly Dictionary<long,int>? _categoryOrder;
        private readonly Dictionary<long,int>? _areaOrder;
        public KeyPathOrderComparer(List<string> dims, Dictionary<long,int>? categoryOrder, Dictionary<long,int>? areaOrder)
        { _dims = dims; _categoryOrder = categoryOrder; _areaOrder = areaOrder; }

        public int Compare(List<string>? x, List<string>? y)
        {
            if (ReferenceEquals(x, y)) return 0;
            if (x == null) return -1; if (y == null) return 1;
            int n = Math.Min(x.Count, y.Count);
            for (int i = 0; i < n; i++)
            {
                var dim = (i < _dims.Count) ? _dims[i] : string.Empty;
                var a = x[i] ?? string.Empty;
                var b = y[i] ?? string.Empty;
                int c = CompareByDim(dim, a, b);
                if (c != 0) return c;
            }
            return x.Count.CompareTo(y.Count);
        }

        // Expose for sibling nested comparers (e.g., CycleTreeNodeComparer) within PlanViewService.
        // The containing type is private, so widening to internal keeps visibility scoped to this outer class.
        internal static int NaturalCompare(string? a, string? b)
        {
            // Simple natural comparer: compare sequences of digits as numbers
            if (ReferenceEquals(a, b)) return 0; a ??= string.Empty; b ??= string.Empty;
            int i = 0, j = 0; int na = a.Length, nb = b.Length;
            while (i < na && j < nb)
            {
                var ca = a[i]; var cb = b[j];
                bool da = char.IsDigit(ca); bool db = char.IsDigit(cb);
                if (da && db)
                {
                    long va = 0, vb = 0;
                    int si = i; while (i < na && char.IsDigit(a[i])) { va = va * 10 + (a[i] - '0'); i++; }
                    int sj = j; while (j < nb && char.IsDigit(b[j])) { vb = vb * 10 + (b[j] - '0'); j++; }
                    int c = va.CompareTo(vb); if (c != 0) return c;
                    // if numbers equal, shorter digit run sorts first (e.g., 001 < 01 < 1)
                    c = (i - si).CompareTo(j - sj); if (c != 0) return c;
                }
                else
                {
                    if (ca != cb) return ca.CompareTo(cb);
                    i++; j++;
                }
            }
            return na.CompareTo(nb);
        }

        private int CompareByDim(string dim, string a, string b)
        {
            switch (dim)
            {
                case "ship":
                case "section":
                    return NaturalCompare(a, b);
                case "sectionFirst":
                    {
                        bool ad = a.Length > 0 && char.IsDigit(a[0]);
                        bool bd = b.Length > 0 && char.IsDigit(b[0]);
                        if (ad && bd)
                        {
                            int ai = a[0] - '0';
                            int bi = b[0] - '0';
                            return ai.CompareTo(bi);
                        }
                        if (ad != bd) return ad ? -1 : 1; // 数字优先
                        return string.Compare(a, b, StringComparison.Ordinal);
                    }
                case "category":
                    {
                        if (long.TryParse(a, out var aid) && long.TryParse(b, out var bid) && _categoryOrder != null)
                        {
                            int ao = _categoryOrder.TryGetValue(aid, out var av) ? av : int.MaxValue;
                            int bo = _categoryOrder.TryGetValue(bid, out var bv) ? bv : int.MaxValue;
                            int c = ao.CompareTo(bo);
                            if (c != 0) return c;
                        }
                        return string.Compare(a, b, StringComparison.Ordinal);
                    }
                case "region":
                    {
                        if (long.TryParse(a, out var aid) && long.TryParse(b, out var bid) && _areaOrder != null)
                        {
                            int ao = _areaOrder.TryGetValue(aid, out var av) ? av : int.MaxValue;
                            int bo = _areaOrder.TryGetValue(bid, out var bv) ? bv : int.MaxValue;
                            int c = ao.CompareTo(bo);
                            if (c != 0) return c;
                        }
                        return string.Compare(a, b, StringComparison.Ordinal);
                    }
                default:
                    return string.Compare(a, b, StringComparison.Ordinal);
            }
        }
    }

    /// <summary>
    /// 树节点排序（用于父层集合排序）。与 KeyPathOrderComparer 同口径。
    /// </summary>
    private sealed class CycleTreeNodeComparer : IComparer<CycleTreeNode>
    {
        private readonly string _dim;
        private readonly Dictionary<long,int>? _categoryOrder;
        private readonly Dictionary<long,int>? _areaOrder;
        public CycleTreeNodeComparer(string dim, Dictionary<long,int>? categoryOrder, Dictionary<long,int>? areaOrder)
        { _dim = dim; _categoryOrder = categoryOrder; _areaOrder = areaOrder; }

        public int Compare(CycleTreeNode? x, CycleTreeNode? y)
        {
            if (ReferenceEquals(x, y)) return 0;
            if (x == null) return -1; if (y == null) return 1;
            var a = (x.KeyPath.Count > 0) ? x.KeyPath.Last() : string.Empty;
            var b = (y.KeyPath.Count > 0) ? y.KeyPath.Last() : string.Empty;
            switch (_dim)
            {
                case "ship":
                case "section":
                    return KeyPathOrderComparer.NaturalCompare(a, b);
                case "sectionFirst":
                    bool ad = a.Length > 0 && char.IsDigit(a[0]);
                    bool bd = b.Length > 0 && char.IsDigit(b[0]);
                    if (ad && bd)
                    {
                        int ai = a[0] - '0';
                        int bi = b[0] - '0';
                        return ai.CompareTo(bi);
                    }
                    if (ad != bd) return ad ? -1 : 1;
                    return string.Compare(a, b, StringComparison.Ordinal);
                case "category":
                    if (long.TryParse(a, out var aid) && long.TryParse(b, out var bid) && _categoryOrder != null)
                    {
                        int ao = _categoryOrder.TryGetValue(aid, out var av) ? av : int.MaxValue;
                        int bo = _categoryOrder.TryGetValue(bid, out var bv) ? bv : int.MaxValue;
                        int c = ao.CompareTo(bo);
                        if (c != 0) return c;
                    }
                    return string.Compare(a, b, StringComparison.Ordinal);
                case "region":
                    if (long.TryParse(a, out var ra) && long.TryParse(b, out var rb) && _areaOrder != null)
                    {
                        int ao = _areaOrder.TryGetValue(ra, out var av) ? av : int.MaxValue;
                        int bo = _areaOrder.TryGetValue(rb, out var bv) ? bv : int.MaxValue;
                        int c = ao.CompareTo(bo);
                        if (c != 0) return c;
                    }
                    return string.Compare(a, b, StringComparison.Ordinal);
                default:
                    return string.Compare(a, b, StringComparison.Ordinal);
            }
        }
    }

    private static void FlattenTreeToRows(IEnumerable<CycleTreeNode> nodes, List<CycleTreeExportRow> acc)
    {
        foreach (var n in nodes)
        {
            acc.Add(NodeToRow(n));
            if (n.Children != null && n.Children.Count > 0) FlattenTreeToRows(n.Children, acc);
        }
    }

    private static CycleTreeExportRow NodeToRow(CycleTreeNode n)
    {
        return new CycleTreeExportRow
        {
            Level = n.Level,
            Ship = n.Labels.ContainsKey("ship") ? n.Labels["ship"] : null,
            Section = n.Labels.ContainsKey("section") ? n.Labels["section"] : null,
            SectionFirst = n.Labels.ContainsKey("sectionFirst") ? n.Labels["sectionFirst"] : null,
            Category = n.Labels.ContainsKey("category") ? n.Labels["category"] : null,
            Region = n.Labels.ContainsKey("region") ? n.Labels["region"] : null,
            ItemCount = n.ItemCount,
            CompletedCount = n.CompletedCount,
            InProgressCount = n.InProgressCount,
            PlanCycleDays = n.PlanCycleAvgDays,
            ActualCycleDays = n.ActualCycleAvgDays,
            PlanCycleSumDays = n.PlanCycleSumDays,
            ActualCycleSumDays = n.ActualCycleSumDays,
            VarianceDays = n.VarianceAvgDays,
            VarianceSumDays = n.VarianceSumDays,
            PlanSpanDays = n.PlanSpanDays,
            ActualSpanDays = n.ActualSpanDays
        };
    }

    /// <summary>
    /// 构建基础查询条件 - 性能优化版本
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    private ISugarQueryable<PlanList> BuildBaseQuery(PlanViewInput input)
    {
        var query = _sqlSugarClient.Queryable<PlanList>();
        
        // 筛选区域,分类 - 使用索引友好的查询方式
        query = query
            .WhereIF(input.ClassName != null, u => u.ClassName == input.ClassName)
            .WhereIF(input.WorkAreaId != null, u => u.WorkAreaId == input.WorkAreaId);

        // 关键词搜索优化 - 使用优化的OR条件查询
        if (!string.IsNullOrWhiteSpace(input.Keyword))
        {
            var keyword = input.Keyword.Trim();
            
            // 使用优化的OR条件，但添加索引提示
            query = query.Where(u => 
                u.Ch.Contains(keyword) || 
                u.Fd.Contains(keyword) || 
                u.Team.Contains(keyword) || 
                u.Remark.Contains(keyword)
            );
        }

        // 移除时间范围筛选 - 应该获取所有数据，在统计阶段按事件发生日期进行筛选
        // 这样可以确保统计的准确性，避免遗漏跨业务月的计划项目

        return query.ClearFilter<IOrgIdFilter>();
    }

    /// <summary>
    /// 构建基础查询条件 - 简化版本，用于不需要关键词搜索的场景
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    private ISugarQueryable<PlanList> BuildSimpleBaseQuery(PlanViewInput input)
    {
        var query = _sqlSugarClient.Queryable<PlanList>();
        
        // 基础筛选条件
        query = query
            .WhereIF(input.ClassName != null, u => u.ClassName == input.ClassName)
            .WhereIF(input.WorkAreaId != null, u => u.WorkAreaId == input.WorkAreaId);

        // 移除时间范围筛选 - 应该获取所有数据，在统计阶段按事件发生日期进行筛选
        // 这样可以确保统计的准确性，避免遗漏跨业务月的计划项目

        return query.ClearFilter<IOrgIdFilter>();
    }

    /// <summary>
    /// 获取统计数据的缓存键 - 优化版本
    /// </summary>
    /// <param name="input"></param>
    /// <param name="type"></param>
    /// <returns></returns>
    private string GetStatisticsCacheKey(PlanViewInput input, string type)
    {
        // 简化缓存键，提高命中率
        var workTime = input.workTime ?? "all";
        var className = input.ClassName?.ToString() ?? "all";
        var workAreaId = input.WorkAreaId?.ToString() ?? "all";
        var keyword = string.IsNullOrWhiteSpace(input.Keyword) ? "none" : "search";
        
        return $"PlanView_{type}_{workTime}_{className}_{workAreaId}_{keyword}";
    }


    /// <summary>
    /// 获取更新信息分析数据 📈
    /// </summary>
    /// <param name="input">查询参数</param>
    /// <returns></returns>
    [DisplayName("获取更新信息分析数据")]
    [ApiDescriptionSettings(Name = "GetUpdateAnalysisData"), HttpPost]
    public async Task<List<UpdateAnalysisOutput>> GetUpdateAnalysisData(UpdateAnalysisInput input)
    {
        input.workTime = input.workTime?.Trim();
        
        // 尝试从缓存获取
        var cacheKey = GetUpdateAnalysisCacheKey(input);
        var cachedResult = _sysCacheService.Get<List<UpdateAnalysisOutput>>(cacheKey);
        if (cachedResult != null)
        {
            return cachedResult;
        }

        // 构建基础查询
        var query = BuildBaseQuery(new PlanViewInput 
        { 
            workTime = input.workTime, 
            ClassName = input.ClassName, 
            WorkAreaId = input.WorkAreaId 
        });

        // 获取今天的日期范围
        var today = DateTime.Today;
        var tomorrow = today.AddDays(1);
        
        // 使用窗口函数获取每个作业区的最新记录信息
        var sql = @"
        WITH LatestRecords AS (
            SELECT 
                p.WorkAreaId,
                o.Name as WorkAreaName,
                p.UpdateTime,
                p.CreateTime,
                p.UpdateUserName,
                ROW_NUMBER() OVER (PARTITION BY p.WorkAreaId ORDER BY COALESCE(p.UpdateTime, p.CreateTime) DESC) as rn
            FROM plan_List p
            LEFT JOIN SysOrg o ON p.WorkAreaId = o.Id
            WHERE o.Name LIKE '%作业区%'
                AND p.IsDelete = 0
        ),
        StatisticsData AS (
            SELECT 
                p.WorkAreaId,
                MAX(o.Name) as WorkAreaName,
                COUNT(CASE WHEN p.UpdateTime >= @today AND p.UpdateTime < @tomorrow THEN p.Id END) as TodayUpdateCount,
                MAX(p.CreateTime) as LastCreateTime,
                MAX(p.UpdateTime) as LastUpdateTime
            FROM plan_List p
            LEFT JOIN SysOrg o ON p.WorkAreaId = o.Id
            WHERE o.Name LIKE '%作业区%'
                AND p.IsDelete = 0
            GROUP BY p.WorkAreaId
        )
        SELECT 
            s.WorkAreaId,
            s.WorkAreaName,
            s.TodayUpdateCount,
            s.LastCreateTime,
            s.LastUpdateTime,
            l.UpdateUserName as LastUpdateUserName
        FROM StatisticsData s
        LEFT JOIN LatestRecords l ON s.WorkAreaId = l.WorkAreaId AND l.rn = 1
        ORDER BY COALESCE(s.LastUpdateTime, s.LastCreateTime) DESC";

        var updateAnalysisData = await _sqlSugarClient.SqlQueryable<dynamic>(sql)
            .AddParameters(new { today = today, tomorrow = tomorrow })
            .ToListAsync();

        // 转换为输出DTO
        var result = updateAnalysisData.Select(item => {
            var lastUpdateTime = item.LastUpdateTime as DateTime?;
            var lastCreateTime = item.LastCreateTime as DateTime?;
            
            // 计算今日更新状态
            var hasUpdateToday = lastUpdateTime.HasValue && 
                                lastUpdateTime.Value >= today && 
                                lastUpdateTime.Value < tomorrow;
            
            return new UpdateAnalysisOutput
            {
                WorkAreaId = item.WorkAreaId as long?,
                WorkAreaName = item.WorkAreaName?.ToString() ?? "未知区域",
                TotalCount = Convert.ToInt32(item.TodayUpdateCount), // 只统计今日更新的数量
                LastCreateTime = lastCreateTime,
                LastUpdateTime = lastUpdateTime,
                LastUpdateUserName = item.LastUpdateUserName?.ToString(), // 使用最新记录的更新人
                TodayUpdateStatus = hasUpdateToday ? "已更新" : "未更新"
            };
        }).ToList();

        // 设置缓存 5 分钟
        _sysCacheService.Set(cacheKey, result, TimeSpan.FromMinutes(5));
        
        return result;
    }

    /// <summary>
    /// 获取更新分析缓存键
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    private string GetUpdateAnalysisCacheKey(UpdateAnalysisInput input)
    {
        var workTime = input.workTime ?? "all";
        var className = input.ClassName?.ToString() ?? "all";
        var workAreaId = input.WorkAreaId?.ToString() ?? "all";
        
        return $"PlanView_UpdateAnalysis_{workTime}_{className}_{workAreaId}";
    }


    /// <summary>
    /// 获取计划清单数据（分页）
    /// </summary>
    /// <param name="input">查询参数</param>
    /// <returns>分页的计划清单数据</returns>
    [DisplayName("获取计划清单数据")]
    [HttpPost]
    public async Task<PlanListPageOutput> GetPlanLists(PlanListInput input)
    {
        input.workTime = input.workTime?.Trim();
        
        // 计算时间范围：优先使用前端传入的显式范围
        DateTime startDate, endDate;
        if (input.RangeStart.HasValue && input.RangeEnd.HasValue)
        {
            startDate = input.RangeStart.Value;
            endDate = input.RangeEnd.Value;
        }
        else
        {
            (startDate, endDate) = DateTimeUtil.CalculateWorkTimeRange(input.workTime);
        }
        
        // 构建基础查询
        var query = _sqlSugarClient.Queryable<PlanList>()
            .LeftJoin<SysOrg>((p, o) => p.WorkAreaId == o.Id)
            .LeftJoin<PlanType>((p, o, pt) => p.ClassName == pt.Id)
            .WhereIF(input.ClassName != null, (p, o, pt) => p.ClassName == input.ClassName)
            .WhereIF(input.WorkAreaId != null, (p, o, pt) => p.WorkAreaId == input.WorkAreaId);
        
        // 所有清单类型都需要基础时间筛选：PlanStart或PlanEnd在月份范围内
        query = query.Where((p, o, pt) => 
            (p.PlanStart >= startDate && p.PlanStart <= endDate) ||
            (p.PlanEnd >= startDate && p.PlanEnd <= endDate));

        // 计算截止日期（与PlanDelayViewsService保持一致）
        var today = DateTime.Now.Date;
        bool isCurrentWorkMonth = today >= startDate && today <= endDate.Date;
        bool isFutureMonth = startDate > today;
        var cutoffDate = isCurrentWorkMonth && !isFutureMonth ? today.AddDays(1).AddMilliseconds(-1) : endDate.Date;
        
        // 根据清单类型筛选数据 - 修正逻辑与看板统计保持一致
        switch (input.listType?.ToLower())
        {
            case "actualstart":
                // 实际开工清单：计划开工日期在业务月份范围内 + 有实际开工时间的记录
                // 对应看板统计：实际开工/计划开工含提前开工项 的实际开工数（118）
                query = query.Where((p, o, pt) => 
                    p.PlanStart != null && 
                    p.PlanStart >= startDate && 
                    p.PlanStart <= endDate &&
                    p.ActualStart != null);
                break;
            case "actualend":
                // 实际完工清单：计划完工日期在业务月份范围内 + 有实际完工时间的记录
                // 对应看板统计：实际完工/计划完工含提前完工项 的实际完工数
                query = query.Where((p, o, pt) => 
                    p.PlanEnd != null && 
                    p.PlanEnd >= startDate && 
                    p.PlanEnd <= endDate &&
                    p.ActualEnd != null);
                break;
            case "delaystart":
                // 到时间未开工清单：与统计卡片逻辑完全一致
                // 1. 基础数据集已经筛选了 (PlanStart >= startDate && PlanStart <= endDate) || (PlanEnd >= startDate && PlanEnd <= endDate)
                // 2. 进一步筛选：PlanStart在月份内 + PlanStart <= cutoffDate + 无实际开工
                // 开工统计：只统计计划开工日期在时间范围内且已延期的记录（计划日期 < 今天）
                query = query.Where((p, o, pt) => 
                    p.PlanStart != null && 
                    p.PlanStart >= startDate && 
                    p.PlanStart <= endDate &&
                    p.PlanStart < DateTime.Now.Date && 
                    p.ActualStart == null);
                
                // 添加延期范围筛选
                if (input.DelayRange.HasValue && input.DelayRange != DelayRangeEnum.All)
                {
                    var todayForDelay = DateTime.Now.Date;
                    switch (input.DelayRange)
                    {
                        case DelayRangeEnum.Light: // 1-3天
                            query = query.Where((p, o, pt) => 
                                p.PlanStart.HasValue &&
                                SqlFunc.DateDiff(DateType.Day, p.PlanStart.Value, todayForDelay) >= 1 && 
                                SqlFunc.DateDiff(DateType.Day, p.PlanStart.Value, todayForDelay) <= 3);
                            break;
                        case DelayRangeEnum.Medium: // 4-7天
                            query = query.Where((p, o, pt) => 
                                p.PlanStart.HasValue &&
                                SqlFunc.DateDiff(DateType.Day, p.PlanStart.Value, todayForDelay) >= 4 && 
                                SqlFunc.DateDiff(DateType.Day, p.PlanStart.Value, todayForDelay) <= 7);
                            break;
                        case DelayRangeEnum.Severe: // 7天以上
                            query = query.Where((p, o, pt) => 
                                p.PlanStart.HasValue &&
                                SqlFunc.DateDiff(DateType.Day, p.PlanStart.Value, todayForDelay) > 7);
                            break;
                    }
                }
                break;
            case "delayend":
                // 到时间未完工清单：与统计卡片逻辑完全一致
                // 1. 基础数据集已经筛选了 (PlanStart >= startDate && PlanStart <= endDate) || (PlanEnd >= startDate && PlanEnd <= endDate)
                // 2. 进一步筛选：PlanEnd在月份内 + PlanEnd < 今天 + 无实际完工（已延期）
                query = query.Where((p, o, pt) => 
                    p.PlanEnd != null && 
                    p.PlanEnd >= startDate && 
                    p.PlanEnd <= endDate &&
                    p.PlanEnd < DateTime.Now.Date && 
                    p.ActualEnd == null);
                
                // 添加延期范围筛选
                if (input.DelayRange.HasValue && input.DelayRange != DelayRangeEnum.All)
                {
                    var todayForEndDelay = DateTime.Now.Date;
                    switch (input.DelayRange)
                    {
                        case DelayRangeEnum.Light: // 1-3天
                            query = query.Where((p, o, pt) => 
                                p.PlanEnd.HasValue &&
                                SqlFunc.DateDiff(DateType.Day, p.PlanEnd.Value, todayForEndDelay) >= 1 && 
                                SqlFunc.DateDiff(DateType.Day, p.PlanEnd.Value, todayForEndDelay) <= 3);
                            break;
                        case DelayRangeEnum.Medium: // 4-7天
                            query = query.Where((p, o, pt) => 
                                p.PlanEnd.HasValue &&
                                SqlFunc.DateDiff(DateType.Day, p.PlanEnd.Value, todayForEndDelay) >= 4 && 
                                SqlFunc.DateDiff(DateType.Day, p.PlanEnd.Value, todayForEndDelay) <= 7);
                            break;
                        case DelayRangeEnum.Severe: // 7天以上
                            query = query.Where((p, o, pt) => 
                                p.PlanEnd.HasValue &&
                                SqlFunc.DateDiff(DateType.Day, p.PlanEnd.Value, todayForEndDelay) > 7);
                            break;
                    }
                }
                break;
            default:
                // 默认返回所有数据
                break;
        }

        // 对延期数据添加排序（延期时间越久的在前面）
        if (input.listType == "delaystart")
        {
            query = query.OrderBy((p, o, pt) => p.PlanStart);
        }
        else if (input.listType == "delayend")
        {
            query = query.OrderBy((p, o, pt) => p.PlanEnd);
        }

        // 统计总数
        var totalCount = await query.CountAsync();

        // 获取分页数据 - 使用与PlanListService相同的方式
        var items = await query
            .Select((p, o, pt) => new PlanListItemDto
            {
                Ch = p.Ch ?? "",
                Fd = p.Fd ?? "",
                ClassName = pt.Name ?? "",
                WorkAreaName = o.Name ?? "",
                Team = p.Team ?? "",
                PlanStart = p.PlanStart,
                ActualStart = p.ActualStart,
                PlanEnd = p.PlanEnd,
                ActualEnd = p.ActualEnd,
                // 计算延期天数
                DelayDays = input.listType == "delaystart" 
                    ? (p.PlanStart.HasValue ? SqlFunc.DateDiff(DateType.Day, p.PlanStart.Value, DateTime.Now.Date) : 0)
                    : input.listType == "delayend" 
                        ? (p.PlanEnd.HasValue ? SqlFunc.DateDiff(DateType.Day, p.PlanEnd.Value, DateTime.Now.Date) : 0)
                        : 0
            })
            .ToPageListAsync(input.pageIndex, input.pageSize);

        return new PlanListPageOutput
        {
            Items = items,
            Total = totalCount,
            PageIndex = input.pageIndex,
            PageSize = input.pageSize
        };
    }

    /// <summary>
    /// 导出计划清单Excel
    /// </summary>
    /// <param name="input">查询参数</param>
    /// <returns>Excel文件</returns>
    [DisplayName("导出计划清单Excel")]
    [HttpPost]
    public async Task<IActionResult> ExportPlanListsExcel(PlanListInput input)
    {
        input.workTime = input.workTime?.Trim();
        
        // 计算时间范围：优先使用前端传入的显式范围
        DateTime startDate, endDate;
        if (input.RangeStart.HasValue && input.RangeEnd.HasValue)
        {
            startDate = input.RangeStart.Value;
            endDate = input.RangeEnd.Value;
        }
        else
        {
            (startDate, endDate) = DateTimeUtil.CalculateWorkTimeRange(input.workTime);
        }
        
        // 构建基础查询 - 不分页，获取所有数据
        var query = _sqlSugarClient.Queryable<PlanList>()
            .LeftJoin<SysOrg>((p, o) => p.WorkAreaId == o.Id)
            .LeftJoin<PlanType>((p, o, pt) => p.ClassName == pt.Id)
            .WhereIF(input.ClassName != null, (p, o, pt) => p.ClassName == input.ClassName)
            .WhereIF(input.WorkAreaId != null, (p, o, pt) => p.WorkAreaId == input.WorkAreaId);

        // 根据清单类型筛选数据 - 与查询接口保持一致的逻辑
        switch (input.listType?.ToLower())
        {
            case "actualstart":
                // 实际开工清单：计划开工日期在业务月份范围内 + 有实际开工时间的记录
                query = query.Where((p, o, pt) => 
                    p.PlanStart != null && 
                    p.PlanStart >= startDate && 
                    p.PlanStart <= endDate &&
                    p.ActualStart != null);
                break;
            case "actualend":
                // 实际完工清单：计划完工日期在业务月份范围内 + 有实际完工时间的记录
                query = query.Where((p, o, pt) => 
                    p.PlanEnd != null && 
                    p.PlanEnd >= startDate && 
                    p.PlanEnd <= endDate &&
                    p.ActualEnd != null);
                break;
            case "delaystart":
                // 到时间未开工清单：筛选月份内 + 计划开工时间已延期但未开工
                query = query.Where((p, o, pt) => 
                    p.PlanStart != null && 
                    p.PlanStart >= startDate && 
                    p.PlanStart <= endDate &&
                    p.PlanStart < DateTime.Now.Date && 
                    p.ActualStart == null);
                break;
            case "delayend":
                // 到时间未完工清单：筛选月份内 + 计划完工时间已延期但未完工
                query = query.Where((p, o, pt) => 
                    p.PlanEnd != null && 
                    p.PlanEnd >= startDate && 
                    p.PlanEnd <= endDate &&
                    p.PlanEnd < DateTime.Now.Date && 
                    p.ActualEnd == null);
                break;
        }

        // 获取所有数据用于导出
        var allItems = await query
            .Select((p, o, pt) => new PlanListItemDto
            {
                Ch = p.Ch ?? "",
                Fd = p.Fd ?? "",
                ClassName = pt.Name ?? "",
                WorkAreaName = o.Name ?? "",
                Team = p.Team ?? "",
                PlanStart = p.PlanStart,
                ActualStart = p.ActualStart,
                PlanEnd = p.PlanEnd,
                ActualEnd = p.ActualEnd
            })
            .ToListAsync();

        if (allItems.Count == 0)
        {
            throw new Exception("暂无数据可导出");
        }

        // 创建Excel数据
        var excelData = allItems.Select((item, index) => new
        {
            序号 = index + 1,
            船号 = item.Ch,
            分段 = item.Fd,
            分类 = item.ClassName,
            区域 = item.WorkAreaName,
            劳务队 = item.Team,
            计划开工 = item.PlanStart?.ToString("yyyy-MM-dd"),
            实际开工 = item.ActualStart?.ToString("yyyy-MM-dd"),
            计划完工 = item.PlanEnd?.ToString("yyyy-MM-dd"),
            实际完工 = item.ActualEnd?.ToString("yyyy-MM-dd")
        });

        // 生成Excel文件名
        var listTypeNames = new Dictionary<string, string>
        {
            ["actualstart"] = "实际开工清单",
            ["actualend"] = "实际完工清单",
            ["delaystart"] = "到时间未开工清单",
            ["delayend"] = "到时间未完工清单"
        };
        
        var listTypeName = listTypeNames.GetValueOrDefault(input.listType?.ToLower(), "计划清单");
        var fileName = $"{listTypeName}_{input.workTime}_{DateTime.Now:yyyyMMdd}.xlsx";

        // 使用OfficeOpenXml生成Excel
        using var package = new OfficeOpenXml.ExcelPackage();
        var worksheet = package.Workbook.Worksheets.Add(listTypeName);

        // 设置表头
        var headers = new[] { "序号", "船号", "分段", "分类", "区域", "劳务队", "计划开工", "实际开工", "计划完工", "实际完工" };
        for (int i = 0; i < headers.Length; i++)
        {
            worksheet.Cells[1, i + 1].Value = headers[i];
            worksheet.Cells[1, i + 1].Style.Font.Bold = true;
            worksheet.Cells[1, i + 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            worksheet.Cells[1, i + 1].Style.Fill.BackgroundColor.SetColor(255, 211, 211, 211); // 浅灰色 (alpha, red, green, blue)
        }

        // 填充数据
        var row = 2;
        foreach (var item in excelData)
        {
            worksheet.Cells[row, 1].Value = item.序号;
            worksheet.Cells[row, 2].Value = item.船号;
            worksheet.Cells[row, 3].Value = item.分段;
            worksheet.Cells[row, 4].Value = item.分类;
            worksheet.Cells[row, 5].Value = item.区域;
            worksheet.Cells[row, 6].Value = item.劳务队;
            worksheet.Cells[row, 7].Value = item.计划开工;
            worksheet.Cells[row, 8].Value = item.实际开工;
            worksheet.Cells[row, 9].Value = item.计划完工;
            worksheet.Cells[row, 10].Value = item.实际完工;
            row++;
        }

        // 自动调整列宽
        worksheet.Cells.AutoFitColumns();

        // 设置边框
        var range = worksheet.Cells[1, 1, row - 1, headers.Length];
        range.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
        range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
        range.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
        range.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;

        // 返回Excel文件
        var excelBytes = package.GetAsByteArray();
        return new FileContentResult(excelBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        {
            FileDownloadName = fileName
        };
    }

    #region 新的统一API接口 - 重构版本

    /// <summary>
    /// 获取统计卡片数据
    /// 用于StatisticsCards.vue组件显示统计卡片和时间进度条
    /// </summary>
    /// <param name="filter">筛选条件</param>
    /// <returns>统计卡片响应</returns>
    [DisplayName("获取统计卡片数据")]
    [HttpPost("statisticsCards")]
    public async Task<StatisticsCardsResponse> GetStatisticsCards(BusinessMonthFilter filter)
    {
        filter.WorkTime = filter.WorkTime?.Trim();

        // 生成缓存键
        var cacheKey = GenerateCacheKey("StatisticsCards", filter);
        
        // 尝试从缓存获取
        if (_sysCacheService.ExistKey(cacheKey))
            return _sysCacheService.Get<StatisticsCardsResponse>(cacheKey);

        try
        {
            // 获取统计卡片完整数据（包含计划和延期统计）
            var response = await _statisticsCardService.GetCardsDataAsync(filter);

            // 缓存结果（2分钟）
            _sysCacheService.Set(cacheKey, response, TimeSpan.FromMinutes(2));
            return response;
        }
        catch (Exception ex)
        {
            throw new Exception($"获取统计卡片数据失败: {ex.Message}", ex);
        }
    }

    /// <summary>
    /// 获取月度图表数据
    /// 用于CombinedAreaChart.vue组件显示月度累计趋势图
    /// </summary>
    /// <param name="filter">筛选条件</param>
    /// <returns>月度图表响应</returns>
    [DisplayName("获取月度图表数据")]
    [HttpPost("monthlyChart")]
    public async Task<MonthlyChartResponse> GetMonthlyChart(BusinessMonthFilter filter)
    {
        filter.WorkTime = filter.WorkTime?.Trim();

        // 生成缓存键
        var cacheKey = GenerateCacheKey("MonthlyChart", filter);
        
        // 尝试从缓存获取
        if (_sysCacheService.ExistKey(cacheKey))
            return _sysCacheService.Get<MonthlyChartResponse>(cacheKey);

        try
        {
            // 获取月度图表数据
            var monthlyData = await _monthlyChartService.GetMonthlyDataAsync(filter);
            var dateRange = _dataFilterService.GetDateRange(filter.WorkTime);
            var (startDate, endDate) = (dateRange.Item1, dateRange.Item2);

            var response = new MonthlyChartResponse
            {
                MonthlyData = monthlyData,
                BusinessMonthStart = startDate,
                BusinessMonthEnd = endDate,
                LoadTime = DateTime.Now,
                CacheExpiry = DateTime.Now.AddMinutes(2)
            };

            // 缓存结果（2分钟）
            _sysCacheService.Set(cacheKey, response, TimeSpan.FromMinutes(2));
            return response;
        }
        catch (Exception ex)
        {
            throw new Exception($"获取月度图表数据失败: {ex.Message}", ex);
        }
    }

    /// <summary>
    /// 获取区域分析数据 - 区域Tab使用
    /// 懒加载，只在Tab点击时调用
    /// </summary>
    /// <param name="filter">筛选条件</param>
    /// <returns>区域分析响应</returns>
    [DisplayName("获取区域分析数据")]
    [HttpPost("areaAnalysis")]
    public async Task<AreaAnalysisResponse> GetAreaAnalysis(BusinessMonthFilter filter)
    {
        filter.WorkTime = filter.WorkTime?.Trim();

        // 生成缓存键
        var cacheKey = GenerateCacheKey("Areas", filter);
        
        // 尝试从缓存获取
        if (_sysCacheService.ExistKey(cacheKey))
            return _sysCacheService.Get<AreaAnalysisResponse>(cacheKey);

        try
        {
            var areaData = await _areaAnalysisService.GetAreaDataAsync(filter);

            // 计算汇总数据
            var summary = CalculateAreaSummary(areaData);

            var response = new AreaAnalysisResponse
            {
                Areas = areaData,
                Summary = summary,
                LastUpdateTime = DateTime.Now
            };

            // 缓存2分钟
            _sysCacheService.Set(cacheKey, response, TimeSpan.FromMinutes(2));
            return response;
        }
        catch (Exception ex)
        {
            throw new Exception($"获取区域分析数据失败: {ex.Message}", ex);
        }
    }

    /// <summary>
    /// 获取分类分析数据 - 分类Tab使用
    /// 懒加载，只在Tab点击时调用
    /// </summary>
    /// <param name="filter">筛选条件</param>
    /// <returns>分类分析响应</returns>
    [DisplayName("获取分类分析数据")]
    [HttpPost("categoryAnalysis")]
    public async Task<CategoryAnalysisResponse> GetCategoryAnalysis(BusinessMonthFilter filter)
    {
        filter.WorkTime = filter.WorkTime?.Trim();

        // 生成缓存键
        var cacheKey = GenerateCacheKey("Categories", filter);
        
        // 尝试从缓存获取
        if (_sysCacheService.ExistKey(cacheKey))
            return _sysCacheService.Get<CategoryAnalysisResponse>(cacheKey);

        try
        {
            var categoryData = await _categoryAnalysisService.GetCategoryDataAsync(filter);

            // 计算汇总数据
            var summary = CalculateCategorySummary(categoryData);

            var response = new CategoryAnalysisResponse
            {
                Categories = categoryData,
                Summary = summary,
                LastUpdateTime = DateTime.Now
            };

            // 缓存2分钟
            _sysCacheService.Set(cacheKey, response, TimeSpan.FromMinutes(2));
            return response;
        }
        catch (Exception ex)
        {
            throw new Exception($"获取分类分析数据失败: {ex.Message}", ex);
        }
    }

    /// <summary>
    /// 生成统一缓存键
    /// </summary>
    /// <param name="prefix">缓存前缀</param>
    /// <param name="filter">筛选条件</param>
    /// <returns>缓存键</returns>
    private string GenerateCacheKey(string prefix, BusinessMonthFilter filter)
    {
        // 当提供 RangeStart/RangeEnd 时，以起止范围构建缓存键，避免同一月份不同显式范围冲突
        var workTime = (filter.RangeStart.HasValue && filter.RangeEnd.HasValue)
            ? $"{filter.RangeStart.Value:yyyyMMddHHmmss}_{filter.RangeEnd.Value:yyyyMMddHHmmss}"
            : (filter.WorkTime ?? "all");
        var className = filter.ClassName?.ToString() ?? "all";
        var workAreaId = filter.WorkAreaId?.ToString() ?? "all";
        var keyword = string.IsNullOrWhiteSpace(filter.Keyword) ? "none" : "search";
        
        return $"PlanView_{prefix}_{workTime}_{className}_{workAreaId}_{keyword}";
    }

    /// <summary>
    /// 计算区域汇总数据
    /// </summary>
    /// <param name="areaData">区域数据</param>
    /// <returns>区域汇总</returns>
    private AreaSummary CalculateAreaSummary(List<AreaItem> areaData)
    {
        if (!areaData.Any())
            return new AreaSummary();

        var totalPlans = areaData.Sum(x => x.TotalPlans);
        var totalCompleted = areaData.Sum(x => x.CompletedPlans);
        var overallRate = totalPlans > 0 ? Math.Round((decimal)totalCompleted / totalPlans * 100, 2) : 0;

        var bestArea = areaData.OrderByDescending(x => x.CompletionRate).FirstOrDefault();

        return new AreaSummary
        {
            TotalAreas = areaData.Count,
            TotalPlans = totalPlans,
            TotalCompleted = totalCompleted,
            OverallCompletionRate = overallRate,
            BestAreaId = bestArea?.AreaId,
            BestAreaName = bestArea?.AreaName ?? "",
            BestAreaRate = bestArea?.CompletionRate ?? 0
        };
    }

    /// <summary>
    /// 计算分类汇总数据
    /// </summary>
    /// <param name="categoryData">分类数据</param>
    /// <returns>分类汇总</returns>
    private CategorySummary CalculateCategorySummary(List<CategoryItem> categoryData)
    {
        if (!categoryData.Any())
            return new CategorySummary();

        var totalPlans = categoryData.Sum(x => x.TotalPlans);
        var totalCompleted = categoryData.Sum(x => x.CompletedPlans);
        var overallRate = totalPlans > 0 ? Math.Round((decimal)totalCompleted / totalPlans * 100, 2) : 0;

        var bestCategory = categoryData.OrderByDescending(x => x.CompletionRate).FirstOrDefault();

        return new CategorySummary
        {
            TotalCategories = categoryData.Count,
            TotalPlans = totalPlans,
            TotalCompleted = totalCompleted,
            OverallCompletionRate = overallRate,
            BestCategoryId = bestCategory?.CategoryId,
            BestCategoryName = bestCategory?.CategoryName ?? "",
            BestCategoryRate = bestCategory?.CompletionRate ?? 0
        };
    }


    /// <summary>
    /// 计算时间进度率
    /// </summary>
    /// <param name="startDate">业务月开始日期</param>
    /// <param name="endDate">业务月结束日期</param>
    /// <returns>时间进度率（百分比）</returns>
    private static decimal CalculateTimeProgressRate(DateTime startDate, DateTime endDate)
    {
        var today = DateTime.Now.Date;
        var totalDays = (endDate.Date - startDate.Date).Days + 1;
        
        if (today < startDate.Date)
        {
            return 0; // 还未开始
        }
        
        if (today > endDate.Date)
        {
            return 100; // 已结束
        }
        
        var passedDays = (today - startDate.Date).Days + 1;
        return Math.Round((decimal)passedDays / totalDays * 100, 2);
    }

    #endregion

}
